1

I use this select from Know relationships between all the tables of database in SQL Server stackoverflow question to find all relationships exists in my database.

SELECT
    fk.name 'FK Name',
    tp.name 'Parent table',
    cp.name, cp.column_id,
    tr.name 'Refrenced table',
    cr.name, cr.column_id
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN 
    sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN 
    sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN 
    sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
    tp.name, cp.column_id

But how to add type of the relationship such M:M M:1, 1:1 to the results? which sql property or column say that?

Jon Sud
  • 10,211
  • 17
  • 76
  • 174
  • 2
    Between any 2 tables it is impossible to have a M:M relationship. To support that type of relationship you must have a third table (often referred to as an intersection table). – SMor Jan 08 '21 at 17:26

2 Answers2

1

There is no SQL property that tracks what you are requesting because those relationships only occur implicity, if at all.

As described in this post, it is "technically impossible" to have a 1:1 relationship, so don't worry about that one.
M:M cannot occur, as the Primary key MUST be unique.
M:1 always exists where the 1 is a unique primary key and M is a foreign key, given the foreign key does not also have a UNIQUE KEY constraint (if it does, the relationship is 1:0..1 as the previously mentioned post explains).

The closest thing I can come up with as a built-in property is sys.key_constraints.

UPDATE 2021.01.12

To clarify, while 1:1 relationships cannot exist in a Relational SQL database (again, see this post for an explanation), they can still exist logically within code and design.

Connor Low
  • 5,900
  • 3
  • 31
  • 52
  • So how in typeorm they say 1:1 is possible? https://typeorm.io/#/one-to-one-relations – Jon Sud Jan 09 '21 at 09:38
  • That is a loose 1:1 relationship defined by the program, not by the database. ORMs like this one add in constraints on the application side to enforce behavior (which means you could still INSERT a `User` directly and the DB would accept the record). – Connor Low Jan 12 '21 at 18:27
0

There is no way SQL Server knows type of relationships, but here is how to discover them :

  1. 1:1 : if the relationship is between a PRIMARY KEY and UNIQUE FOREIGN KEY (Unique index can be separate as well)

so have to find out out if there is unique index on the FK or it's been defined as unique fk

  1. 1:M : if the relationship is between a PRIMARY KEY and FOREIGN KEY

so if the oneside of the relationship is a primary key in that table it is a one to many relationship.

  1. M:M : if the relationship is between a non-unique key and non-unique foreign key.

if it doesn't any of two types above then It's many to many

eshirvana
  • 23,227
  • 3
  • 22
  • 38