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

  • 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?
  • 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

  • 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

  • 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) A socialist is someone who give you the shirt off *someone else's* back.

  • 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