Foreign Keys

  • -- Here is a nice little utility that gives you more than the INFORMATION_SCHEMA.

    /*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    -- Use this to check foreign key constraints.

    -- Create a USE <database>; GO for the database you need to check.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

    SELECT

    RFNGT.name AS 'Referencing Table',

    RFNGT.object_id AS 'Referencing Object ID',

    RFCDT.name AS 'Referenced Table Name',

    RFCDC.object_id AS 'Referenced Table ID',

    RFCDC.name AS 'Referenced Column Name',

    FKC.name AS 'Foreign Key Name',

    FKC.object_id AS 'Key ID Value',

    FKC.type_desc

    FROM

    sys.tables RFNGT

    JOIN

    sys.foreign_keys FKC

    ON

    FKC.parent_object_id = RFNGT.object_id

    JOIN

    sys.tables RFCDT

    ON

    RFCDT.object_id = FKC.referenced_object_id

    JOIN

    sys.columns RFNGC

    ON

    RFNGC.object_id = RFNGT.object_id

    JOIN

    sys.columns RFCDC

    ON

    RFCDT.object_id = RFCDC.object_id

    JOIN

    sys.foreign_key_columns FKCC

    ON

    FKCC.constraint_object_id = FKC.object_id

    AND

    RFNGT.object_id = FKCC.parent_object_id

    AND

    RFNGC.column_id = FKCC.parent_column_id

    AND

    RFCDT.object_id = FKCC.referenced_object_id

    AND

    RFCDC.column_id = FKCC.referenced_column_id

    JOIN

    sys.schemas RFNGS

    ON

    RFNGS.schema_id = RFNGT.schema_id

    JOIN

    sys.schemas RFCDS

    ON

    RFCDS.schema_id = RFCDT.schema_id

    ORDER BY 'Referencing Table' -- RFCDT.name

  • Or this:

    SELECT

    OBJECT_NAME(fkc.parent_object_id) AS [Referencing Table],

    fkc.parent_object_id AS [Referencing Object ID],

    OBJECT_NAME(fkc.referenced_object_id) AS [Referenced Table Name],

    fkc.referenced_object_id AS [Referenced Table ID],

    COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS [Referenced Column Name],

    OBJECT_NAME(fkc.constraint_object_id) AS [Foreign Key Name],

    fkc.constraint_object_id AS [Key ID Value],

    'FOREIGN_KEY_CONSTRAINT' AS type_desc

    FROM sys.foreign_key_columns fkc

    ORDER BY [Referencing Table], [Foreign Key Name]

    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".

  • Thanks Scott ... yours is much easier and simpler. No joins ... I need to look more at those specialized system tables! Good job.

  • Now, here is a prototype FKC (Foreign Key Constraint) Remover / Rebuilder. It is not fully functional, but I will post updates, and those keeners out there must have a better way.

    The Use Case helps in the final stages of DB Development, where a developer in the construction phase (Unified Process) can easily drop or re-create the existing FKC's (Foreign Key Contraints).

    For me, I usually create or script out a completely separate code base or script to "initialize" and encapsulate all the FKC create scripts. Nowadays, I use the Database Project template in Visual Studio. Here is an example of a create script for one foreign key constraint:

    IF NOT EXISTS (SELECT * FROM sys.foreign_keys

    WHERE object_id = OBJECT_ID(N'<Schema>.<ForeignKeyConstraintName>')

    AND parent_object_id = OBJECT_ID(N'<Schema>.<TableName>'))

    BEGIN

    ALTER TABLE <Schema>.<TableName> WITH CHECK ADD CONSTRAINT

    <DesiredForeignKeyName> FOREIGN KEY(ReferencingForeignKeyColumn) REFERENCES <ReferencedSchema>.<ReferencedTable>(<ReferencedForeignKeyColumnName>)

    END

    GO

    -- Still to Do (updates welcome - even a different approach)

    -- 1. Create a backup process for a coded FK Creator from existing code. Use Dynamic SQL.

    -- 2. The loop is not checking against already deleted constraints.

    -- 3. I need to create a table variable to hold the existing structures.

    -- 4. Consume the table variable to hold the current information.

    -- 5. Provide a parameter to rebuild or remove the keys with a backup.

    -- Note: I know this is like publish / or Schema Compare, but this code should work.

    -- Will run as a batch from here ...

    IF OBJECT_ID(N'dbo.DropForeignKeys') IS NOT NULL -- Schema.Procedure

    BEGIN

    DROP PROCEDURE dbo.DropForeignKeys;

    PRINT 'Stored Procedure dbo.DropForeignKeys was successfully Dropped'

    END

    GO

    CREATE PROCEDURE dbo.DropForeignKeys

    @SchemaTable_IN AS VARCHAR(50),

    @Cycles AS TINYINT = 6,

    @WithCodedBackup AS BIT = 0,

    @CodedBackupSchema AS VARCHAR(40) = NULL,

    @CodedBackupTable AS VARCHAR(40) = NULL,

    @Messages_OUT AS VARCHAR(MAX) = 'Message on the OUTPUT Parameter' OUTPUT

    AS

    SET NOCOUNT ON

    SET ANSI_NULLS ON -- NULL Comparison behaviour

    SET QUOTED_IDENTIFIER ON -- delimter wrapping behaviour

    -- Variables Section

    DECLARE

    @Remover AS VARCHAR(MAX),

    @i AS TINYINT = 0,

    @SchemaTable AS VARCHAR(50),

    @Object AS INTEGER;

    DECLARE @BackupCode AS TABLE (

    ForeignKeySchemaPlusName VARCHAR(60),

    ParentTableObjectName (50) VARCHAR(60),

    ParentTableReferencedColumn

    );

    SET @SchemaTable = @SchemaTable_IN

    SET @Object = OBJECT_ID(@SchemaTable)

    PRINT CAST(@Object AS VARCHAR(15))

    PRINT @SchemaTable

    PRINT 'Cycles: ' + CAST( @Cycles AS VARCHAR(3))

    -- Code Blocks

    BEGIN TRY

    WHILE @i <= @Cycles -- One of the tables has two FKCs

    BEGIN

    PRINT 'Entered Loop'

    IF EXISTS(SELECT 1 FROM sys.foreign_keys WHERE parent_object_id = @Object)

    BEGIN

    SELECT @Remover ='ALTER TABLE ' + @SchemaTable + ' DROP CONSTRAINT [' + LTRIM(RTRIM([name])) + ']'

    FROM sys.foreign_keys

    WHERE parent_object_id = @Object

    --EXECUTE (@Remover)

    END

    SET @i = @i + 1 -- + 1

    END

    END TRY

    -- Error Trapping

    BEGIN CATCH

    SET @Messages_OUT = 'There was an error.'

    END CATCH

    GO

    IF OBJECT_ID(N'dbo.DropForeignKeys') IS NOT NULL PRINT 'Stored Procedure dbo.DropForeignKeys was successfully Created.'

    GO

    -- Will run as a batch to here ...

    -- Testing Harness follows.

    -- To do:

    -- 1. Add parameters (BITS) to rebuild or remove.

    -- 2. Instead of Cycles (loop cycles), I want to use a TABLE variable to hold or build history.

    -- 2. Test thoroughly and debug. I am currently using PRINT statements, but there is a better way.

    DECLARE

    @SchemaTable_IN VARCHAR(50), @ReturnValue_IN INTEGER, @Messages VARCHAR(MAX), @Cycles TINYINT

    EXECUTE dbo.DropForeignKeys

    @SchemaTable_IN = 'Know.PersonSoftwareJunction',

    @Cycles = 6,

    @Messages_OUT = @Messages OUTPUT

    BEGIN;

    PRINT 'Return Code: ' + CAST(@ReturnValue_IN AS VARCHAR(10))

    PRINT ISNULL(@Messages, 'No Message')

    END;

  • Looking at this again ... it's insane! But, it's a start.

  • I found the following script while google'ing the other day to script out the drop and creates of all the FK's in a DB.

    Apologies to the source, I can't remember off the top of my head where I got the script from

    CREATE TABLE #x -- feel free to use a permanent table

    (

    drop_script NVARCHAR(MAX),

    create_script NVARCHAR(MAX)

    );

    DECLARE @drop NVARCHAR(MAX) = N'',

    @create NVARCHAR(MAX) = N'';

    -- drop is easy, just build a simple concatenated list from sys.foreign_keys:

    SELECT @drop += N'

    ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)

    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'

    FROM sys.foreign_keys AS fk

    INNER JOIN sys.tables AS ct

    ON fk.parent_object_id = ct.[object_id]

    INNER JOIN sys.schemas AS cs

    ON ct.[schema_id] = cs.[schema_id];

    INSERT #x(drop_script) SELECT @drop;

    -- create is a little more complex. We need to generate the list of

    -- columns on both sides of the constraint, even though in most cases

    -- there is only one column.

    SELECT @create += N'

    ALTER TABLE '

    + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)

    + ' ADD CONSTRAINT ' + QUOTENAME(fk.name)

    + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)

    -- get all the columns in the constraint table

    FROM sys.columns AS c

    INNER JOIN sys.foreign_key_columns AS fkc

    ON fkc.parent_column_id = c.column_id

    AND fkc.parent_object_id = c.[object_id]

    WHERE fkc.constraint_object_id = fk.[object_id]

    ORDER BY fkc.constraint_column_id

    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')

    + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)

    + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)

    -- get all the referenced columns

    FROM sys.columns AS c

    INNER JOIN sys.foreign_key_columns AS fkc

    ON fkc.referenced_column_id = c.column_id

    AND fkc.referenced_object_id = c.[object_id]

    WHERE fkc.constraint_object_id = fk.[object_id]

    ORDER BY fkc.constraint_column_id

    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'

    FROM sys.foreign_keys AS fk

    INNER JOIN sys.tables AS rt -- referenced table

    ON fk.referenced_object_id = rt.[object_id]

    INNER JOIN sys.schemas AS rs

    ON rt.[schema_id] = rs.[schema_id]

    INNER JOIN sys.tables AS ct -- constraint table

    ON fk.parent_object_id = ct.[object_id]

    INNER JOIN sys.schemas AS cs

    ON ct.[schema_id] = cs.[schema_id]

    WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

    UPDATE #x SET create_script = @create;

    PRINT @drop;

    PRINT @create;

    /*

    EXEC sp_executesql @drop

    -- clear out data etc. here

    EXEC sp_executesql @create;

    */

    Edit, my Google-Foo amazes me yet again, first link in my search

    https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

  • I'm curious. Why would you need to drop and recreate the Foreign Key constraints?

    If it's for renaming purposes, I would simply rename them.

    If it's for rebuilding the indexes, I would simply rebuild the indexes.

    You could also check them if they were disabled, but if that's needed on a database level, I'm assuming a greater problem is behind this and we'd be only attacking the symptom instead of the root cause.

    I used this for renaming purposes:

    -------------------------------------------------------------------------------------------------

    --The following script renames the constraints with the following naming convention:

    -- Primary Keys: PK_TableName

    -- Foreign Keys: FK_TableName_ReferencedTableName[_ColumnName] (Column name is only included when a table is referenced more than once)

    -- Defaults: DF_TableName_ColumnName

    -- Unique Constraints: UQ_TableName[_Num] (Adds a numeral if more than one UQs exist in a table)

    -- Check Constraints: CK_TableName_ColumnName

    -------------------------------------------------------------------------------------------------

    DECLARE @SQL varchar(600);

    DECLARE RenamingCur CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY

    FOR

    ----Foreign Keys

    SELECT 'EXEC sp_rename ' +

    QUOTENAME( f.name, '''') + ', ' +

    QUOTENAME( 'FK_' + OBJECT_NAME( f.parent_object_id) + '_' + OBJECT_NAME( f.referenced_object_id)

    + CASE WHEN COUNT(*) OVER( PARTITION BY f.parent_object_id, f.referenced_object_id) > 1

    THEN '_' + COL_NAME(fc.parent_object_id, fc.parent_column_id)

    ELSE '' END, '''') + ', ''OBJECT'';'

    FROM sys.foreign_keys f

    JOIN sys.objects r ON f.referenced_object_id = r.object_id

    JOIN sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id

    UNION ALL

    ----Defaults

    SELECT 'EXEC sp_rename ' +

    QUOTENAME( d.name, '''') + ', ' +

    QUOTENAME( 'DF_' + OBJECT_NAME( d.parent_object_id) + '_' + COL_NAME(d.parent_object_id, d.parent_column_id), '''') + ', ''OBJECT'';'

    FROM sys.default_constraints d

    UNION ALL

    ----Primary Keys

    SELECT 'EXEC sp_rename ' +

    QUOTENAME( d.name, '''') + ', ' +

    QUOTENAME( 'PK_' + OBJECT_NAME( d.parent_object_id), '''') + ', ''OBJECT'';'

    FROM sys.key_constraints d

    WHERE type = 'PK'

    UNION ALL

    ----Unique Constraints

    SELECT 'EXEC sp_rename ' +

    QUOTENAME( d.name, '''') + ', ' +

    QUOTENAME( 'UQ_' + OBJECT_NAME( d.parent_object_id)

    + ISNULL( NULLIF( '_' + CAST( ROW_NUMBER() OVER(PARTITION BY d.parent_object_id ORDER BY i.column_id) AS VARCHAR(3)), '1'), ''), '''')

    + ', ''OBJECT'';'

    FROM sys.key_constraints d

    JOIN sys.index_columns i ON d.parent_object_id = i.object_id AND d.unique_index_id = i.index_id AND key_ordinal = 1

    WHERE d.type = 'UQ'

    UNION ALL

    ----Check Constraints

    SELECT 'EXEC sp_rename ' +

    QUOTENAME( d.name, '''') + ', ' +

    QUOTENAME( 'CK_' + OBJECT_NAME( d.parent_object_id) + '_' + COL_NAME(d.parent_object_id, d.parent_column_id), '''') + ', ''OBJECT'';'

    FROM sys.check_constraints d;

    OPEN RenamingCur;

    FETCH NEXT FROM RenamingCur INTO @SQL;

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC(@SQL);

    FETCH NEXT FROM RenamingCur INTO @SQL;

    END

    CLOSE RenamingCur;

    DEALLOCATE RenamingCur;

    --SELECT object_id,

    -- type,

    -- name

    --FROM sys.objects

    --WHERE type IN( 'C ', 'PK', 'UQ', 'F ', 'D ' )

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Fwiw, here's my version of it, with an optional TRUNCATE (currently commented out) of the tables in between the DROP and ADD. I use this script, for example, to remove all data from tables so I'm left with only the structure after a RESTORE (into a dev or qa environment).

    The results are very similar to the other script (edit: Anthony's posting), of course, except that:

    (1) the table names are in alpha order and in ref name order within the same table

    (2) it includes the ON DELETE and ON UPDATE options

    (3) it includes the "NOT FOR REPLICATION" option, if specified in the original FK

    SELECT

    CASE WHEN constraint_action = 'TRUNCATE' THEN

    'TRUNCATE TABLE [' + SCHEMA_NAME(o_par.schema_id) + '].[' + o_par.name + '] '

    ELSE

    'ALTER TABLE [' + SCHEMA_NAME(o_par.schema_id) + '].[' + o_par.name + '] ' +

    constraint_action + ' CONSTRAINT [' + fk.name + '] ' +

    CASE WHEN constraint_action = 'DROP' THEN ''

    WHEN constraint_action = 'ADD' THEN

    'FOREIGN KEY (' + SUBSTRING((

    SELECT ', ' + COL_NAME(fkc.parent_object_id, fkc.parent_column_id)

    FROM sys.foreign_key_columns fkc

    WHERE

    fkc.constraint_object_id = fk.object_id

    ORDER BY

    fkc.constraint_column_id

    FOR XML PATH('')

    ), 3, 4000) + ') ' +

    'REFERENCES [' + SCHEMA_NAME(o_ref.schema_id) + '].[' + o_ref.name + '] (' + SUBSTRING((

    SELECT ', ' + COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id)

    FROM sys.foreign_key_columns fkc

    WHERE

    fkc.constraint_object_id = fk.object_id

    ORDER BY

    fkc.constraint_column_id

    FOR XML PATH('')

    ), 3, 4000) + ') ' +

    'ON DELETE ' + REPLACE(delete_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS, '_', ' ') + ' ' +

    'ON UPDATE ' + REPLACE(update_referential_action_desc COLLATE SQL_Latin1_General_CP1_CI_AS, '_', ' ') + ' ' +

    CASE WHEN fk.is_not_for_replication = 1 THEN 'NOT FOR REPLICATION' ELSE '' END

    END

    END AS [--FK_DROP_TRUNC_ADD]

    FROM sys.foreign_keys fk

    INNER JOIN sys.objects o_par ON

    o_par.object_id = fk.parent_object_id

    INNER JOIN sys.objects o_ref ON

    o_ref.object_id = fk.referenced_object_id

    INNER JOIN (

    SELECT 'DROP' AS constraint_action, 1 AS sort_sequence

    --UNION ALL --uncomment these lines to also TRUNCATE the tables

    --SELECT 'TRUNCATE', 2 --uncomment these lines to also TRUNCATE the tables

    UNION ALL

    SELECT 'ADD', 3

    ) AS constraint_controls ON

    constraint_controls.constraint_action IN ('DROP', 'ADD') OR

    fk.object_id = (SELECT MIN(fk2.object_id) FROM sys.foreign_keys fk2 WHERE fk2.parent_object_id = fk.parent_object_id)

    WHERE

    fk.is_ms_shipped = 0

    ORDER BY

    sort_sequence, o_par.name, o_ref.name

    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".

  • Thanks Scott. The original use case had something to do with a logging harness for SSIS, which was spawned from a text book example based on an early WROX publication, written by that guru of all SSIS (I can find a better reference later). I will have to find the ultimate author to give more details on that. That whole technique has now replaced by the new SSISDB API (SQL). Essentially, I used a fully scripted SQL Solution that allowed me to develop the solution and test data loading with SSIS packages. The logging harness was completely disposable, in that the data was not really critical during the development phases. Dropping and re-creating the foreign key constraints just became part of my workflow. It's "baggage" that gets carried around until a better technique is found to do the containment of whatever is in the baggage.

Viewing 9 posts - 1 through 9 (of 9 total)

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