Thursday 3 May 2007

Referential Integrity via Information_Schema views

Tables with Primary Keys -
-- Tables with Primary Keys defined
-- Note : Multiple rows are returned when the PK involves more than one column
SELECT  TC.TABLE_NAME
      ,CU.COLUMN_NAME
      ,TC.CONSTRAINT_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
              ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
             AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'

Tables with Foreign Keys -
                                   
-- Tables with Foreign Keys defined
-- Note : Multiple rows are returned when the FK involves more than one colum
SELECT  TC.TABLE_NAME
      ,CU.COLUMN_NAME
      ,TC.CONSTRAINT_NAME
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
      INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
              ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
             AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

How the keys are linked -
                                
-- How Referential Integrity is enforced
-- i.e. data being present in related table before insert is allow
SELECT  UNIQUE_CONSTRAINT_NAME AS PRIMARY_KEY_CONSTRAINT
      ,CONSTRAINT_NAME        AS FOREIGN_KEY_CONSTRAINT
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Greater detail about the FK to PK Relationships.
Includes Table and Column information.
                                
-- How Referential Integrity is enforced
-- Expand to show referenced columns
SELECT   CONSTRAINTSLINK.CONSTRAINT_NAME        AS FOREIGN_KEY_CONSTRAINT
        ,FOREIGNKEY.TABLE_NAME                  AS REFERENCINGTABLE
        ,FOREIGNKEY.COLUMN_NAME                 AS REFERENCINGCOLUMN
        ,CONSTRAINTSLINK.UNIQUE_CONSTRAINT_NAME AS PRIMARY_KEY_CONSTRAINT
        ,PRIMARYKEY.TABLE_NAME                  AS REFERENCEDTABLE
        ,PRIMARYKEY.COLUMN_NAME                 AS REFERENCEDCOLUMN
FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CONSTRAINTSLINK
        INNER JOIN (SELECT TC.TABLE_NAME
                           ,UC.COLUMN_NAME
                           ,TC.CONSTRAINT_NAME
                    FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                           INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE UC
                                   ON TC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
                                  AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY') PRIMARYKEY
          ON CONSTRAINTSLINK.UNIQUE_CONSTRAINT_NAME = PRIMARYKEY.CONSTRAINT_NAME
        INNER JOIN (SELECT TC.TABLE_NAME
                           ,UC.COLUMN_NAME
                           ,TC.CONSTRAINT_NAME
                    FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
                           INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE UC
                                   ON TC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
                                  AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY') FOREIGNKEY
          ON CONSTRAINTSLINK.CONSTRAINT_NAME = FOREIGNKEY.CONSTRAINT_NAME
                                              
ORDER BY  CONSTRAINTSLINK.CONSTRAINT_NAME
        ,FOREIGNKEY.TABLE_NAME
        ,FOREIGNKEY.COLUMN_NAME

No comments: