Sql-server – Cascade primary key update to all referencing foreign keys

foreign keyreferential-integritysql serversql-server-2008update

Is it possible to update a primary key column value with cascading the update among all the foreign keys referencing it ?

# EDIT 1:
When I run followinq query

select * from sys.foreign_keys where referenced_object_id=OBJECT_ID('myTable')

, I see that update_referential_action is set to 0. Thus NO ACTION is taken after updating my primary keys columns. 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 @fkCol sysname;

DECLARE @pkCol 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 ColumnCursor 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 ColumnCursor;

        SET @col1 = 1;

        FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        WHILE @@FETCH_STATUS = 0

        BEGIN

            IF (@col1 = 1)

                SET @col1 = 0;

            ELSE

            BEGIN

                SET @tsql = @tsql + ',';

                SET @tsql2 = @tsql2 + ',';

            END;

            SET @tsql = @tsql + QUOTENAME(@fkCol);

            SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);

            FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;

        END;

        CLOSE ColumnCursor;

        DEALLOCATE ColumnCursor;

       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;

To generate the DROP foreign keys script, modify @action value to be equal to 'DROP' in the declaration clause :

DECLARE @action char(6) = 'DROP';

Best Answer

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint.

If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update.

EDIT: Since you do not have the ON UPDATE CASCADE 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 each table with the FK:

ALTER TABLE to drop the existing FK constraint.
ALTER TABLE again to create the ON UPDATE CASCADE constraint for the FK in question.

This takes a bit of effort, but would result in your constraint being properly set for your case.

EDIT 2: The information that you need is found in sys.foreign_keys. You can select from that table to get all the information you need.

A post from John Paul Cook can be found here:

(http://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx)

This code will drop and create ALL FK constraints in a database. You should be able to work from that to make only the changes that you want in your database.

Related Solutions

Sql-server – Find the Foreign Keys Associated with a Given Primary Key

Here's a simple query to match up foreign keys to their referenced tables/columns:

SELECT
    o1.name AS FK_table,
    c1.name AS FK_column,
    fk.name AS FK_name,
    o2.name AS PK_table,
    c2.name AS PK_column,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.foreign_key_columns fkc
        ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.columns c1
        ON fkc.parent_object_id = c1.object_id
        AND fkc.parent_column_id = c1.column_id
    INNER JOIN sys.columns c2
        ON fkc.referenced_object_id = c2.object_id
        AND fkc.referenced_column_id = c2.column_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
ORDER BY o1.name, o2.name, fkc.constraint_column_id

The output has eight columns: the table and column names for the foreign keys (FK_table, FK_column), the names of the foreign-key constraints (FK_name), the referenced PK or unique index table and column names (PK_table, PK_column), the name of the referenced PK or unique index (PK_name), and the update/delete cascade actions (Delete_Action, Update_Action).

(Edited to add some more output columns.)

EDIT: I'm back 6 years later with an improved version of this. I realized that the original query doesn't really handle multi-column foreign keys well, and I also wanted to be able to quickly identify disabled, untrusted, or unindexed foreign keys. So here's the new version that corrects all of that.

Multi-column keys are shown as comma-separated lists in FK_columns and PK_columns, using the traditional FOR XML/STUFF abuse. The FK_indexes column shows the names of any indexes on the foreign-key table that could potentially be used to satisfy seeks using the foreign-key columns (mainly for optimizing deletes or updates to the primary key table). If it's NULL, then you've got an unindexed foreign key. You can tweak the ORDER BY, or add a WHERE clause (commented out below) if you want to sort by the PK table name, filter for specific PK/FK tables, etc.

SELECT
    fk.is_disabled,
    fk.is_not_trusted,
    OBJECT_SCHEMA_NAME(o1.object_id) AS FK_schema,
    o1.name AS FK_table,
    --Generate list of columns in referring side of foreign key
    STUFF(
        (
            SELECT ', ' + c1.name AS [text()]
            FROM sys.columns c1 INNER
                JOIN sys.foreign_key_columns fkc
                    ON c1.object_id = fkc.parent_object_id
                    AND c1.column_id = fkc.parent_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS FK_columns,
    --Look for any indexes that will fully satisfy the foreign key columns
    STUFF(
        (
            SELECT ', ' + i.name AS [text()]
            FROM sys.indexes i
            WHERE i.object_id = o1.object_id
                AND NOT EXISTS ( --Find foreign key columns that don't match the index key columns
                    SELECT fkc.constraint_column_id, fkc.parent_column_id
                    FROM sys.foreign_key_columns fkc
                    WHERE fkc.constraint_object_id = fk.object_id
                    EXCEPT
                    SELECT ic.key_ordinal, ic.column_id
                    FROM sys.index_columns ic
                    WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
                )
            FOR XML PATH('')
        ), 1, 2, '') AS FK_indexes,
    fk.name AS FK_name,
    OBJECT_SCHEMA_NAME(o2.object_id) AS PK_schema,
    o2.name AS PK_table,
    --Generate list of columns in referenced (i.e. PK) side of foreign key
    STUFF(
        (
            SELECT ', ' + c2.name AS [text()]
            FROM sys.columns c2
                INNER JOIN sys.foreign_key_columns fkc
                    ON c2.object_id = fkc.referenced_object_id
                    AND c2.column_id = fkc.referenced_column_id
            WHERE fkc.constraint_object_id = fk.object_id
            FOR XML PATH('')
        ), 1, 2, '') AS PK_columns,
    pk.name AS PK_name,
    fk.delete_referential_action_desc AS Delete_Action,
    fk.update_referential_action_desc AS Update_Action
FROM sys.objects o1
    INNER JOIN sys.foreign_keys fk
        ON o1.object_id = fk.parent_object_id
    INNER JOIN sys.objects o2
        ON fk.referenced_object_id = o2.object_id
    INNER JOIN sys.key_constraints pk
        ON fk.referenced_object_id = pk.parent_object_id
        AND fk.key_index_id = pk.unique_index_id
--WHERE o2.name = 'Company_Address'
ORDER BY o1.name, o2.name

Mysql – Two primary keys to one foreign key

I would suggest that you either want a single "couple" table instead of two, if the properties of each coupling are sufficiently similar, or a couple table that the two existing types of couple "inherit" from, like so:

RC                  Couple                                Egg
----------          ---------------------------           ----------------
C ID (FK)   ----->  C ID (PK)                    <-----   C ID (FK)
(rc props)    |     (generic couple properties)           (egg properties)
              |
BC            |
----------    |
C ID (FK)   --'
(BC props)

You could still have breeding and racing details for any could in this arrangement (which might actually be fine, of course) but each egg can only be associated with one couple. You won't have problems with overlapping ID ranges as there is only one range of IDs for couples when modeled this way, and all other tables with data relating to couples only have the one FK linking to the could record.

Best Answer

Related Solutions

Sql-server – Find the Foreign Keys Associated with a Given Primary Key

Mysql – Two primary keys to one foreign key

Related Question