Home / single muslim speed dating london / Sql updating tables with foreign keys

Sql updating tables with foreign keys

A table can contain multiple FOREIGN KEY constraints.

If a FOREIGN KEY constraint already exists, you can modify or delete it.

Another table called Employee Details has a foreign key which references employee_id in order to uniquely identify the relationship between both the tables.

The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

How can I update the foreign keys to make them ON CASCADE UPDATE ?

# EDIT 2 : In order to script out creation or dropping of all foreign keys in your schema run the following script (taken from here) DECLARE @schema_name sysname; DECLARE @table_name sysname; DECLARE @constraint_name sysname; DECLARE @constraint_object_id int; DECLARE @referenced_object_name sysname; DECLARE @is_disabled bit; DECLARE @is_not_for_replication bit; DECLARE @is_not_trusted bit; DECLARE @delete_referential_action tinyint; DECLARE @update_referential_action tinyint; DECLARE @tsql nvarchar(4000); DECLARE @tsql2 nvarchar(4000); DECLARE @fk Col sysname; DECLARE @pk Col sysname; DECLARE @col1 bit; DECLARE @action char(6); DECLARE @referenced_schema_name sysname; DECLARE FKcursor CURSOR FOR select OBJECT_SCHEMA_NAME(parent_object_id) , OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id) , object_id , is_disabled, is_not_for_replication, is_not_trusted , delete_referential_action, update_referential_action, OBJECT_SCHEMA_NAME(referenced_object_id) from sys.foreign_keys order by 1,2; OPEN FKcursor; FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name , @referenced_object_name, @constraint_object_id , @is_disabled, @is_not_for_replication, @is_not_trusted , @delete_referential_action, @update_referential_action, @referenced_schema_name; WHILE @@FETCH_STATUS = 0 BEGIN IF @action 'CREATE' SET @tsql = 'ALTER TABLE ' QUOTENAME(@schema_name) '.' QUOTENAME(@table_name) ' DROP CONSTRAINT ' QUOTENAME(@constraint_name) ';'; ELSE BEGIN SET @tsql = 'ALTER TABLE ' QUOTENAME(@schema_name) '.' QUOTENAME(@table_name) CASE @is_not_trusted WHEN 0 THEN ' WITH CHECK ' ELSE ' WITH NOCHECK ' END ' ADD CONSTRAINT ' QUOTENAME(@constraint_name) ' FOREIGN KEY ('; SET @tsql2 = ''; DECLARE Column Cursor CURSOR FOR select COL_NAME(fk.parent_object_id, fkc.parent_column_id) , COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) from sys.foreign_keys fk inner join sys.foreign_key_columns fkc on fk.object_id = fkc.constraint_object_id where fkc.constraint_object_id = @constraint_object_id order by fkc.constraint_column_id; OPEN Column Cursor; SET @col1 = 1; FETCH NEXT FROM Column Cursor INTO @fk Col, @pk Col; WHILE @@FETCH_STATUS = 0 BEGIN IF (@col1 = 1) SET @col1 = 0; ELSE BEGIN SET @tsql = @tsql ','; SET @tsql2 = @tsql2 ','; END; SET @tsql = @tsql QUOTENAME(@fk Col); SET @tsql2 = @tsql2 QUOTENAME(@pk Col); FETCH NEXT FROM Column Cursor INTO @fk Col, @pk Col; END; CLOSE Column Cursor; DEALLOCATE Column Cursor; SET @tsql = @tsql ' ) REFERENCES ' QUOTENAME(@referenced_schema_name) '.' QUOTENAME(@referenced_object_name) ' (' @tsql2 ')'; SET @tsql = @tsql ' ON UPDATE ' CASE @update_referential_action WHEN 0 THEN 'NO ACTION ' WHEN 1 THEN 'CASCADE ' WHEN 2 THEN 'SET NULL ' ELSE 'SET DEFAULT ' END ' ON DELETE ' CASE @delete_referential_action WHEN 0 THEN 'NO ACTION ' WHEN 1 THEN 'CASCADE ' WHEN 2 THEN 'SET NULL ' ELSE 'SET DEFAULT ' END CASE @is_not_for_replication WHEN 1 THEN ' NOT FOR REPLICATION ' ELSE '' END ';'; END; PRINT @tsql; IF @action = 'CREATE' BEGIN SET @tsql = 'ALTER TABLE ' QUOTENAME(@schema_name) '.' QUOTENAME(@table_name) CASE @is_disabled WHEN 0 THEN ' CHECK ' ELSE ' NOCHECK ' END 'CONSTRAINT ' QUOTENAME(@constraint_name) ';'; PRINT @tsql; END; FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name , @referenced_object_name, @constraint_object_id , @is_disabled, @is_not_for_replication, @is_not_trusted , @delete_referential_action, @update_referential_action, @referenced_schema_name; END; CLOSE FKcursor; DEALLOCATE FKcursor; constraint, but you want to have that set up, it is a bit of work.

SQL Server does not support altering the constraints to a new setting.

It is necessary to iterate through each table that has a FK constraint to the PK table.

For this foreign key, we have specified the ON DELETE CASCADE clause which tells SQL Server to delete the corresponding records in the child table when the data in the parent table is deleted. For this foreign key, we have specified the ON DELETE CASCADE clause which tells SQL Server to delete the corresponding records in the child table when the data in the parent table is deleted.

In this case, I have many tables that need to have a 'commentable' association.

I have a comments table that will have a commentable_id, and many tables that will have a commentable_id, and the commentable_id in the tables will refer to (A.id, in this example).

CONSTRAINT fk_name FOREIGN KEY (child_col1, child_col2, ...

child_col_n) REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n) ON DELETE CASCADE [ ON UPDATE ] ); The columns that you wish to create in the table. The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

566 comments

Leave a Reply

Your email address will not be published. Required fields are marked *

*