CODEWITHSUNDEEP
sqlforeign-keysforeign-key-relationship
Rohit
Rohit

Reputation: 3638

Why does Foreign keys have to relate to only primary keys?

In SQL the foreign keys references primary keys only. Why cannot it refer to a unique not null indexed column that is clustered?

Upvotes: 3

Views: 155

Answers (1)

StuartLC
StuartLC

Reputation: 107247

This is not true of all RDBMS's. SQL Server (2008/R2) allows Foreign Keys on non-null columns which are either Unique Key Constraints or UNIQUE indexes (ie. the candidate key doesn't even need to be the Clustered Index). For example:

CREATE TABLE Parent
(
    ParentUnique1 INT NOT NULL,
    ParentUnique2 INT NOT NULL,
    Name NVARCHAR(50) NOT NULL
);
-- Some arbitrary PK
ALTER TABLE Parent ADD CONSTRAINT PK_Parent PRIMARY KEY(Name);
-- Unique Key Constraint
ALTER TABLE Parent ADD CONSTRAINT U_Parent UNIQUE(ParentUnique1);
-- Unique Index (NonClustered)
CREATE UNIQUE INDEX IX_Parent ON Parent(ParentUnique2);

CREATE TABLE Child1
(
    ChildID INT NOT NULL,
    ParentID INT NOT NULL,
    CONSTRAINT FK_Child1Parent FOREIGN KEY(ParentID) REFERENCES Parent(ParentUnique1)
);

CREATE TABLE Child2
(
    ChildID INT NOT NULL,
    ParentID INT NOT NULL,
    CONSTRAINT FK_Child2Parent FOREIGN KEY(ParentID) REFERENCES Parent(ParentUnique2)
);

Upvotes: 6

Related Questions