can we have a foreign key which is not a primary key in any other table?
it is written in every book that foreign keys are actually primary key in some other table but can we have a foreign key which is not primary key in any other table
3 Solutions collect form web for “can we have a foreign key which is not a primary key in any other table?”
Yes – you can have a foreign key that references a unique index in another table.
CREATE UNIQUE INDEX UX01_YourTable ON dbo.YourTable(SomeUniqueColumn) ALTER TABLE dbo.YourChildTable ADD CONSTRAINT FK_ChildTable_Table FOREIGN KEY(YourFKColumn) REFERENCES dbo.YourTable(SomeUniqueColumn)
By definition a foreign key must reference a candidate key of some table. It doesn’t necessarily have to be the primary key.
As a matter of detail the constraint called a FOREIGN KEY in SQL isn’t exactly equivalent to the textbook definition of a foreign key in the relational model. SQL’s FOREIGN KEY constraint differs because:
- it can reference any set of columns subject to a uniqueness constraint even if they are not candidate keys (superkeys or nullable columns for example).
- it may include nulls, in which case the constraint is not enforced
- its syntax depends on column order, so a fk constraint on (A,B) referencing (A,B) is different to a constraint on (B,A) referencing (A,B).
Yes , There can be a foreign key which is unique key in other table as Unique key is subset of primary key but not the exact primary key.
So that’s possible that foreign key is unique key in aother table.