locked
Discovering Foreign Key Constraints on a Table RRS feed

  • Question

  • I have a denormalized table that provides code values for various lookups.  I am doing a proof of concept to breakout each of the sets of values into their own tables, and I need to delete the data from the old table to prove that it is working right.  I've used a query from Dave Pinal's blog to identify the foreign key constraints that need to be dropped before I can delete the data, but it's not working correctly because when I try to truncate the table SQL keeps telling that there are still foreign key constraints on the table.  Does anyone have a better method of finding all foreign key constraints on a table?

    Thanks for the help!;-)

    Brandon_Forest@sbcglobal.net

    From Dave Pinal's blog:

    SELECT
    CONSTRAINT_NAME = REF_CONST.CONSTRAINT_NAME,
    TABLE_CATALOG = FK.TABLE_CATALOG,
    TABLE_SCHEMA = FK.TABLE_SCHEMA,
    TABLE_NAME = FK.TABLE_NAME,
    COLUMN_NAME = FK_COLS.COLUMN_NAME,
    REFERENCED_TABLE_CATALOG = PK.TABLE_CATALOG,
    REFERENCED_TABLE_SCHEMA = PK.TABLE_SCHEMA,
    REFERENCED_TABLE_NAME = PK.TABLE_NAME,
    REFERENCED_COLUMN_NAME = PK_COLS.COLUMN_NAME
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS REF_CONST
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON REF_CONST.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND REF_CONST.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND REF_CONST.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    AND FK.CONSTRAINT_TYPE = ‘FOREIGN KEY’
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON REF_CONST.UNIQUE_CONSTRAINT_CATALOG = PK.CONSTRAINT_CATALOG
    AND REF_CONST.UNIQUE_CONSTRAINT_SCHEMA = PK.CONSTRAINT_SCHEMA
    AND REF_CONST.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    AND PK.CONSTRAINT_TYPE = ‘PRIMARY KEY’
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE FK_COLS ON REF_CONST.CONSTRAINT_NAME = FK_COLS.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE PK_COLS ON PK.CONSTRAINT_NAME = PK_COLS.CONSTRAINT_NAME

     


    Brandon Forest
    Friday, February 25, 2011 6:14 AM

Answers

  • You can also query directly the sys-catalog views, like:

    select fk.name as fk_name, fk.object_id, object_name(fk.parent_object_id) as table_name,
      col_name(fc.parent_object_id, fc.parent_column_id) as constraint_col_name,
      object_name(fk.referenced_object_id) as referenced_table,
      COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_col_name,
      fk.delete_referential_action_desc
    from sys.foreign_keys fk
    join sys.foreign_key_columns fc
    on fk.object_id = fc.constraint_object_id
    

    ... more on: http://sqlwithmanoj.wordpress.com/2010/12/06/querying-sql-server-metadata/

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Marked as answer by WeiLin Qiao Friday, March 4, 2011 5:36 AM
    Friday, February 25, 2011 6:32 AM
  • SQL Server 2005 here

    You can find all of the columns using:

    SELECT
        [table] = OBJECT_NAME([object_id]),
        [column] = name
    FROM
        sys.columns;

    You can find relationships between tables using:

    SELECT
        [constraint] = name,
        [child_table] = OBJECT_NAME(parent_object_id),
        [parent_table] = OBJECT_NAME(referenced_object_id)
    FROM
        sys.foreign_keys;

    To get the column usage from both sides, the query is a little more
    involved:

    SELECT
          [constraint_name] = f.[name],
          [child_table] = OBJECT_NAME(f.parent_object_id),
          [child_column] = cc.name,
          [parent_table] = OBJECT_NAME(f.referenced_object_id),
          [parent_column] = pc.name
    FROM
          sys.foreign_keys f
    INNER JOIN
    (
          SELECT
                c.[object_id],
                c.name,
                c.column_id,
                ic.index_id
          FROM
                sys.columns c
          INNER JOIN
                sys.index_columns ic
          ON
                c.[object_id] = ic.[object_id]
                AND c.column_id = ic.column_id
    ) AS pc
    ON
          f.key_index_id = pc.index_id
    INNER JOIN
          sys.foreign_key_columns fkc
    ON
          f.[object_id] = fkc.constraint_object_id
          AND pc.[object_id] = fkc.referenced_object_id
          AND fkc.referenced_column_id = pc.column_id
    INNER JOIN
          sys.columns cc
    ON
          fkc.parent_object_id = cc.[object_id]
          AND fkc.parent_column_id = cc.column_id
    ORDER BY
          constraint_name,
          child_table;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Friday, March 4, 2011 5:36 AM
    Friday, February 25, 2011 6:33 AM

All replies

  • You can also query directly the sys-catalog views, like:

    select fk.name as fk_name, fk.object_id, object_name(fk.parent_object_id) as table_name,
      col_name(fc.parent_object_id, fc.parent_column_id) as constraint_col_name,
      object_name(fk.referenced_object_id) as referenced_table,
      COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_col_name,
      fk.delete_referential_action_desc
    from sys.foreign_keys fk
    join sys.foreign_key_columns fc
    on fk.object_id = fc.constraint_object_id
    

    ... more on: http://sqlwithmanoj.wordpress.com/2010/12/06/querying-sql-server-metadata/

     


    ~Manu
    http://sqlwithmanoj.wordpress.com
    MCCA 2011
    • Marked as answer by WeiLin Qiao Friday, March 4, 2011 5:36 AM
    Friday, February 25, 2011 6:32 AM
  • SQL Server 2005 here

    You can find all of the columns using:

    SELECT
        [table] = OBJECT_NAME([object_id]),
        [column] = name
    FROM
        sys.columns;

    You can find relationships between tables using:

    SELECT
        [constraint] = name,
        [child_table] = OBJECT_NAME(parent_object_id),
        [parent_table] = OBJECT_NAME(referenced_object_id)
    FROM
        sys.foreign_keys;

    To get the column usage from both sides, the query is a little more
    involved:

    SELECT
          [constraint_name] = f.[name],
          [child_table] = OBJECT_NAME(f.parent_object_id),
          [child_column] = cc.name,
          [parent_table] = OBJECT_NAME(f.referenced_object_id),
          [parent_column] = pc.name
    FROM
          sys.foreign_keys f
    INNER JOIN
    (
          SELECT
                c.[object_id],
                c.name,
                c.column_id,
                ic.index_id
          FROM
                sys.columns c
          INNER JOIN
                sys.index_columns ic
          ON
                c.[object_id] = ic.[object_id]
                AND c.column_id = ic.column_id
    ) AS pc
    ON
          f.key_index_id = pc.index_id
    INNER JOIN
          sys.foreign_key_columns fkc
    ON
          f.[object_id] = fkc.constraint_object_id
          AND pc.[object_id] = fkc.referenced_object_id
          AND fkc.referenced_column_id = pc.column_id
    INNER JOIN
          sys.columns cc
    ON
          fkc.parent_object_id = cc.[object_id]
          AND fkc.parent_column_id = cc.column_id
    ORDER BY
          constraint_name,
          child_table;


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by WeiLin Qiao Friday, March 4, 2011 5:36 AM
    Friday, February 25, 2011 6:33 AM