Menu
You can use the OBJECTPROPERTY() function in SQL Server to check whether or not a table has one or more foreign key constraints. To do this, pass the table's object ID as the first argument, and TableHasForeignKey as the second argument.
foreign_keys, as well as enabling the constraint. So to see which foreign keys are enabled or disabled, you can check the sys. foreign_keys table on 2005. It has a is_disabled column that has the value of 1 if the foreign key is disabled.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table. The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With