I know how to create one to many, many to many relationships in SQL Server, but is it possible to create one to one relationship? And is it possible to create 1 to 0 or 1 relationship?
Yes, just put
PRIMARY KEYs of both entities into a link table, defining a
UNIQUE key on both entities:
myrel(entityA, entityB, UNIQUE(entityA), UNIQUE(entityB))
entityA = 1 is related to
entityB = 2:
entityA entityB 1 2
, you can relate neither
entityA = 1 to any other
entityB, nor an
entityB = 2 to any other
If you relation is symmetrical (i. e.
entityB belong to same domain and relating
entityB also means relating
entityA), then define an additional
entityA entityB UNIQUE(entityA) UNIQUE(entityB) CHECK(entityA < entityB)
and transform the normalized relation to a canonical one with this query:
SELECT entityA, entityB FROM myrel UNION SELECT entityB, entityA FROM myrel
This is a
If you want it to be a
1:1 relation, define this table to be a domain for both
myrel(entityA, entityB, UNIQUE(entityA), UNIQUE(entityB)) A(id, PRIMARY KEY(id), FOREIGN KEY(id) REFERENCES myrel (entityA)) B(id, PRIMARY KEY(id), FOREIGN KEY(id) REFERENCES myrel (entityB))
By removing the
FOREIGN KEY from either table's definition, you change the corresponding part of the relationship from
Two ways: 1) a pk-pk 1:1 relationship. Table A and B have both a PK. Create an FK from the B PK to the PK of A. This makes 'B' the FK side of the 1:1 relationship
2) an FK/UC-PK 1:1 relationship. Table A has a PK and table B has a foreign key to A, but the FK in B is not on the PK of B. Now create a UC on the FK field(s) in B.
Yes, just make the Primary or alternate Key in the dependant table a Foreign Key to the Primary Key in the parent Table.
It's fun but it my favorite questions on interview.
So You have table A, B corresponding each of them has primary key A_ID, B_ID. Add foreign key to any. Let it be B: A_REF, so you just need add unique constraint onto A_REF.
TableA id PK
TableB id PK FK TableA