Relations in Sql

Question

I know how to create one-many-many-many relationships in SQL Server, but is it possible to create a one-to-one relationship? And is it possible to create relationships from 1 to 0 or 1?

Answer 1

Yes, just put PRIMARY KEYboth objects in the link table, defining a key UNIQUEfor both objects:

myrel(entityA, entityB, UNIQUE(entityA), UNIQUE(entityB))

Thus, if entityA = 1associated with entityB = 2:

entityA  entityB
      1        2

you cannot associate entityA = 1with any other entityB, nor entityB = 2with any other entityA.

(.. entityA entityB , entityA entityB entityB entityA), CHECK constrant

entityA  entityB

UNIQUE(entityA)
UNIQUE(entityB)
CHECK(entityA < entityB)

:

SELECT entityA, entityB
FROM   myrel
UNION
SELECT entityB, entityA
FROM   myrel

(0-1):(0-1).

, 1:1, entityA, 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))

FOREIGN KEY , 1 (0-1).

Answer 2

: 1) pk-pk 1:1. . FK B PK PK A. "B" FK 1:1

2) FK/UC-PK 1:1. A PK, B A, FK B PK B. UC FK B.

Answer 3

Yes, just make the primary or alternate key in the dependent table the foreign key of the primary key in the parent table.

Answer 4

Yes

TableA id PK

TableB id PK FK TableA

Answer 5

It's fun, but these are my favorite interview questions.

So, you have table A, B, each of which has a primary key A_ID, B_ID. Add foreign key to any. Let it be B: A_REF, so you just need to add a unique constraint on A_REF.