CODEWITHSUNDEEP
sqlsql-serverdatabase-design
Arsen Mkrtchyan
Arsen Mkrtchyan

Reputation: 50712

relationships in Sql

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?

Upvotes: 3

Views: 462

Answers (5)

Quassnoi
Quassnoi

Reputation: 425341

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

Upvotes: 3

Ray
Ray

Reputation: 1585

Yes

TableA id PK

TableB id PK FK TableA

Upvotes: 0

Frans Bouma
Frans Bouma

Reputation: 8357

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.

Upvotes: 1

Dewfy
Dewfy

Reputation: 23624

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.

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146469

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

Upvotes: 0

Related Questions