Sql updating tables with foreign keys

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.

Since the purpose of the foreign key is to identify a particular row of the referenced table, it is generally required that the foreign key is equal to the candidate key in some row of the primary table, or else have no value (the NULL value.

sql updating tables with foreign keys-83sql updating tables with foreign keys-50sql updating tables with foreign keys-32sql updating tables with foreign keys-89

In this case, you have Store ID as an attribute of Employee, and Employee ID (the Manager) as an attribute of Store. There are likely better ways to handle such cases, but if your business rules requires such circular dependencies, then it happens.Foreign key definitions for PDF (US Ltr) - 37.5Mb PDF (A4) - 37.5Mb PDF (RPM) - 36.9Mb HTML Download (TGZ) - 10.2Mb HTML Download (Zip) - 10.2Mb HTML Download (RPM) - 8.9Mb Man Pages (TGZ) - 211.3Kb Man Pages (Zip) - 321.0Kb Info (Gzip) - 3.4Mb Info (Zip) - 3.4Mb My SQL Backup and Recovery My SQL Globalization My SQL Information Schema My SQL Installation Guide My SQL and Linux/Unix My SQL and OS X My SQL Partitioning My SQL Performance Schema My SQL Replication Using the My SQL Yum Repository My SQL Restrictions and Limitations Security in My SQL My SQL and Solaris Building My SQL from Source Starting and Stopping My SQL My SQL Tutorial My SQL and Windows My SQL NDB Cluster 7.5 For those encountering the problem " ERROR 1216: Cannot add or update a child row: a foreign key constraint fails", it actually means what it says!Some row in the child does not comply with the constraint, correct the problem.Also, it is interesting to note that while this query works (Note the PRIMARY KEY line): CREATE TABLE `ffxi_character Job` ( `server ID` int(11) NOT NULL, `userid` int(10)unsigned NOT NULL, `character Name` varchar(255) NOT NULL, `job Abbr` char(4) NOT NULL, `job Level` int(11) default '0', PRIMARY KEY (`server ID`,`userid`,`character Name`,`job Abbr`), INDEX (`job Abbr`), CONSTRAINT FOREIGN KEY (`server ID`,`userid`,`character Name`) REFERENCES `ffxi_characters` (`server ID`,`userid`,`character Name`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FOREIGN KEY (`job Abbr`) REFERENCES `ffxi_job Type` (`job Abbr`) ON DELETE CASCADE ON UPDATE CASCADE) TYPE=Inno DB; This query will give you an error 1005 and errno 150: CREATE TABLE `ffxi_character Job` ( `server ID` int(11) NOT NULL, `userid` int(10)unsigned NOT NULL, `character Name` varchar(255) NOT NULL, `job Abbr` char(4) NOT NULL, `job Level` int(11) default '0', PRIMARY KEY (`job Abbr`,`server ID`,`userid`,`character Name`), INDEX (`job Abbr`), CONSTRAINT FOREIGN KEY (`server ID`,`userid`,`character Name`) REFERENCES `ffxi_characters` (`server ID`,`userid`,`character Name`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT FOREIGN KEY (`job Abbr`) REFERENCES `ffxi_job Type` (`job Abbr`) ON DELETE CASCADE ON UPDATE CASCADE) TYPE=Inno DB; In order to make the second one work, you have to add: INDEX (`server ID`,`userid`,`character Name`)before the the foreign key is made.In a previous comment Dennis Haney provided an SQL snippet for finding rows that violate intended foreign key constraints.

Leave a Reply