how to check all the delete and update rules on the foreign keys tables

Question

how to check all the delete and update rules on the foreign keys tables

892717952

SSC Eights!

Points: 843
More actions
December 9, 2020 at 11:11 am

#3818573
below is my questions about foreign key table, thanks!
1. how to get all the delete and update rules on the foreign keys the tables as I plan to delete some data on the table has foreign key?
2. for example, suppose table A is built foreign key on table B , and table B is built foreign key on table C, how can we show the the relationship among these foreign key tables?
December 22, 2020 at 9:40 am

This was removed by the editor as SPAM

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

The lazy programmers' way to delete from complex data structures is to get a data comparison tool to generate the SQL DELETE transaction. Where I work we've used 2 different vendors over time. Redgate makes tools like this. Visual Studio might have this functionality now too. The tools work best when each table has an integer identity primary key. Create a copy of the table without indexes and FK's, with the PK, and without the rows you want to delete. Then compare the tables using the software and create the change script. If it doesn't work place a service call to the vendor 🙂

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

892717952 SSC Eights! Points: 843 More actions · Answer 2

Thanks, I know we can get some informations about foreign key Thru below statement.

SELECT OBJECT_NAME(FK.referenced_object_id) AS 'Referenced Table',

OBJECT_NAME(FK.parent_object_id) AS 'Referring Table',

FK.name AS 'Foreign Key',

COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) AS 'Referenced Column',

COL_NAME(FK.parent_object_id, FKC.parent_column_id) AS 'Referring Column',

FK.name AS 'Foreign Name', FK.create_date,FK.modify_date,

FK.delete_referential_action_desc, FK.update_referential_action_desc

FROM sys.foreign_keys AS FK

INNER JOIN sys.foreign_key_columns AS FKC ON FKC.constraint_object_id = FK.OBJECT_ID

ScottPletcher SSC Guru Points: 101099 More actions · Answer 3

Steve Collins wrote:

The lazy programmers' way to delete from complex data structures is to get a data comparison tool to generate the SQL DELETE transaction. Where I work we've used 2 different vendors over time. Redgate makes tools like this. Visual Studio might have this functionality now too. The tools work best when each table has an integer identity primary key. Create a copy of the table without indexes and FK's, with the PK, and without the rows you want to delete. Then compare the tables using the software and create the change script. If it doesn't work place a service call to the vendor 🙂

That's a ton of work to be the "lazy" way.

The really lazy way -- or really slick way, depending on how you look at it -- is to define the:

FOREIGN KEY with ON DELETE CASCADE /* or ON DELETE SET DEFAULT or ON DELETE SET NULL */

and SQL will automatically delete / update the foreign keys for you if you delete from the other table.

Developers don't realize that ON DELETE NO ACTION /* i.e. a DELETE of a row with matching FKs should be treated as an error*/ is actually only a default and that there is a way to override that so that SQL itself does the work cleaning up the keys.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".