relationships in Sql

Question

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?

Answer 1

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))

Thus, if 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 entityA.

If you relation is symmetrical (i. e. entityA and entityB belong to same domain and relating entityA to entityB also means relating entityB to entityA), then define an additional CHECK constrant:

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 (0-1):(0-1) relation.

If you want it to be a 1:1 relation, define this table to be a domain for both entityA and entityB:

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 1 to (0-1).

Answer 2

Yes, just make the Primary or alternate Key in the dependant table a Foreign Key to the Primary Key in the parent Table.

Answer 3

Yes

TableA id PK

TableB id PK FK TableA

Answer 4

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.

Answer 5

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

or

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.