How to get SQL table foreign key types by a query

Question

I have to display a database diagram on a web application. When the user selects a database table I need to display relation tables and relations types as "one to one", "one to many" or many to many".

So far I was able to get all the details I need except the relationship type using the query below.

But I couldn't find a way to get a relationship type. Can you help me guys?

SELECT 
    t.name AS Parent_TableName,
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) Parent_Id,
    f.Name AS foreign_key_Name,
    OBJECT_NAME(f.parent_object_id) ReferenceTableName,
    COL_NAME(fc.parent_object_id, fc.parent_column_id) ColName
FROM 
    sys.foreign_keys AS f 
INNER JOIN 
    sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
    sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
    OBJECT_NAME (f.referenced_object_id) ='Category'

user_10614091 · Answer

Might it work to look at Unique constraint types on the columns?. In the below, I added a check to see if the constraint type for the parent and child table keys are unique. If they are both unique, then it is a one to one relationship. If one is unique, then it's one to many, if neither is unique then many to many... I'm only demonstrating the check for 'One to One'. If not, I defaulted to 'Other'

UPDATED: I have added a check for "One to Many". I also added a check for "Primary Key" as well as "Unique" constraints.

With tbl As
(
SELECT 
   t.name as Parent_TableName,
   COL_NAME(fc.referenced_object_id,fc.referenced_column_id) Parent_Id,
   f.Name as foreign_key_Name,
   OBJECT_NAME(f.parent_object_id) ReferranceTableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM sys.foreign_keys AS f 
     INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id
     INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id
  WHERE OBJECT_NAME (f.referenced_object_id) ='Category'
)
Select 
   *,Case When 
        (

        Exists 
        (
        SELECT * 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
             inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
             on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
        Where 
             (tc.CONSTRAINT_TYPE = 'Primary Key' Or tc.CONSTRAINT_TYPE = 'Unique') and 
             tc.TABLE_NAME = tbl.Parent_TableName and
             cu.COLUMN_NAME = tbl.Parent_Id
        ) 

        And

        Exists 
        (
        SELECT * 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
             inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
             on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
        Where 
            (tc.CONSTRAINT_TYPE = 'Primary Key' Or tc.CONSTRAINT_TYPE = 'Unique') and 
             tc.TABLE_NAME = tbl.ReferranceTableName and
             cu.COLUMN_NAME = tbl.ColName
        ) 

       ) Then 'One to One' 

/***********Check if the Parent or Child is Unique**Suggestiong a One to Many Relationship********/    
When 
        (  

       Not Exists 
        (
        SELECT * 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
             inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
             on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
        Where 
             (tc.CONSTRAINT_TYPE = 'Primary Key' Or tc.CONSTRAINT_TYPE = 'Unique') and 
             tc.TABLE_NAME = tbl.Parent_TableName and
             cu.COLUMN_NAME = tbl.Parent_Id
        ) 

        And

        Exists 
        (
        SELECT * 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
             inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
             on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
        Where 
             (tc.CONSTRAINT_TYPE = 'Primary Key' Or tc.CONSTRAINT_TYPE = 'Unique') and 
             tc.TABLE_NAME = tbl.ReferranceTableName and
             cu.COLUMN_NAME = tbl.ColName
        ) 

       ) 

       Or  -- Check if the child is unique and the Parent is not
        (

        Exists 
        (
        SELECT * 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
             inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
             on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
        Where 
             (tc.CONSTRAINT_TYPE = 'Primary Key' Or tc.CONSTRAINT_TYPE = 'Unique') and 
             tc.TABLE_NAME = tbl.Parent_TableName and
             cu.COLUMN_NAME = tbl.Parent_Id
        ) 

        And

       Not  Exists 
        (
        SELECT * 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
             inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
             on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
        Where 
             (tc.CONSTRAINT_TYPE = 'Primary Key' Or tc.CONSTRAINT_TYPE = 'Unique') and 
             tc.TABLE_NAME = tbl.ReferranceTableName and
             cu.COLUMN_NAME = tbl.ColName
        ) 

       )       

       Then 'One to Many' 

/******************************************************************/       

         Else 'Other' 
       End As [Relationship] 
From tbl

user_10614091 · Answer

To check the results of the conditions in the relationship checks, lookup the constraints of your tables to see which ones have "Unique" and/or "Primary Key"

To find constraints on your table:

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
     inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
     on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
where 
     tc.TABLE_NAME = 'TABLENAME' and
     cu.COLUMN_NAME = 'COLUMNNAME'

To find the count of Foreign Keys in your table to look for more than one Foreign Key inferring that there might be "Many to Many" relationships:

        SELECT Count(*) 
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc 
             inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu 
             on cu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME 
        Where 
             tc.TABLE_NAME = 'TABLENAME' and
             tc.CONSTRAINT_TYPE = 'FOREIGN KEY'

How to get SQL table foreign key types by a query

2 Answers