問題描述
一種檢查 SQL 2005 中是否存在外鍵的方法 (A way to check if foreign key exists in SQL 2005)
Is there an easy way to check if a foreign key exists for a column in a table? I am writing a script which will add the foreign key only if it does not exist.
‑‑‑‑‑
參考解法
方法 1:
You can use this script:
IF EXISTS (SELECT *
FROM sys.foreign_keys
WHERE object_id = OBJECT_ID(N'[dbo].[FK_NAME]')
AND parent_object_id = OBJECT_ID(N'[dbo].[MyTable]'))
BEGIN
‑‑ do stuff
END
This can be done if you expand out the table and right click on an existing FK and choose script key as "DROP TO" and then you will get a generated script from SQL.
方法 2:
Woo‑hoo! I just spent the past two days doing this.
IF NOT EXISTS ( SELECT name
FROM sys.foreign_keys
WHERE name = 'FK_Name' )
ALTER TABLE table_name ADD CONSTRAINT FK_Name FOREIGN KEY (idcol)
REFERENCES OtherTable(idcol)
(by Barbara Jackson、CodeLikeBeaker、ristonj)