如何以与 MSSQL 2000 和 2005 兼容的方式将这两个 SQL 语句合并为一个？

发布于 2024-07-17 05:20:21 字数 586 浏览 7 评论 0原文

我的最终目标是完成这样的事情：

CREATE FOREIGN KEY IF NOT EXISTS FOREIGN KEY

由于该声明似乎不存在，因此我试图将其拼凑在一起。

我有一个语句，如果存在，它将返回 FK 名称：

SELECT f.name AS ForeignKey
FROM sys.foreign_keys AS f 
WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName'

我有一个添加所需外键的语句：

ALTER TABLE myTableName
WITH CHECK 
ADD CONSTRAINT [FK_myTableName_otherTable] 
FOREIGN KEY([columnName]) 
REFERENCES otherTable ([otherColumn])

我只是无法，在我的一生中，弄清楚如何将它们混合在一起成为一个语句。

绝对要求该解决方案能够在 MS SQL 2000 和 MS SQL 2005 中运行。

原文

My end goal is to accomplish something like:

CREATE FOREIGN KEY IF NOT EXISTS FOREIGN KEY

Since that statement doesn't appear to exist, I'm attempting to sort of kludge it together.

I have a statement that will return the FK name if it exists:

SELECT f.name AS ForeignKey
FROM sys.foreign_keys AS f 
WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName'

And I have a statement that adds the desired foreign key:

ALTER TABLE myTableName
WITH CHECK 
ADD CONSTRAINT [FK_myTableName_otherTable] 
FOREIGN KEY([columnName]) 
REFERENCES otherTable ([otherColumn])

I just can't, for the life of me, figure out how to mash them together into a single statement.

It's an absolute requirement that the solution work in both MS SQL 2000 as well as MS SQL 2005.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情绪失控 2024-07-24 05:20:22

sys.foreign_keys 是在 SQL Server 2005 中引入的您必须使用 sysobjects 来与 SQL Server 2000 和 SQL Server 2000 兼容 2005.

尝试这个 SQL：

IF NOT EXISTS (
    SELECT NULL FROM sysobjects 
    WHERE name = 'FK_myTableName_otherTable' 
    AND parent_obj = OBJECT_ID(N'myTableName'))
ALTER TABLE myTableName
WITH CHECK 
ADD CONSTRAINT [FK_myTableName_otherTable] 
FOREIGN KEY([columnName]) 
REFERENCES otherTable ([otherColumn])

sys.foreign_keys was introduced in SQL Server 2005. You'll have to use sysobjects to be compatible with both SQL Server 2000 & 2005.

Try this SQL:

IF NOT EXISTS (
    SELECT NULL FROM sysobjects 
    WHERE name = 'FK_myTableName_otherTable' 
    AND parent_obj = OBJECT_ID(N'myTableName'))
ALTER TABLE myTableName
WITH CHECK 
ADD CONSTRAINT [FK_myTableName_otherTable] 
FOREIGN KEY([columnName]) 
REFERENCES otherTable ([otherColumn])

回复收藏 0 原文

叶落知秋 2024-07-24 05:20:22

if not exists (
    SELECT f.name AS ForeignKey
    FROM sys.foreign_keys AS f 
    WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName') 
begin
    ALTER TABLE myTableName
    WITH CHECK 
    ADD CONSTRAINT [FK_myTableName_otherTable] 
    FOREIGN KEY([columnName]) 
    REFERENCES otherTable ([otherColumn])
end

if not exists (
    SELECT f.name AS ForeignKey
    FROM sys.foreign_keys AS f 
    WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName') 
begin
    ALTER TABLE myTableName
    WITH CHECK 
    ADD CONSTRAINT [FK_myTableName_otherTable] 
    FOREIGN KEY([columnName]) 
    REFERENCES otherTable ([otherColumn])
end

回复收藏 0 原文

终弃我 2024-07-24 05:20:21

if not exists (SELECT f.name AS ForeignKey
FROM sys.foreign_keys AS f 
WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName'
)
begin
    ALTER TABLE myTableName
    WITH CHECK 
    ADD CONSTRAINT [FK_myTableName_otherTable] 
    FOREIGN KEY([columnName]) 
    REFERENCES otherTable ([otherColumn])
end

if not exists (SELECT f.name AS ForeignKey
FROM sys.foreign_keys AS f 
WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName'
)
begin
    ALTER TABLE myTableName
    WITH CHECK 
    ADD CONSTRAINT [FK_myTableName_otherTable] 
    FOREIGN KEY([columnName]) 
    REFERENCES otherTable ([otherColumn])
end

回复收藏 0 原文

~没有更多了~