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
views:
134answers:
2
+8
A:
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)
marc_s
2010-05-24 06:52:02
This is correct in Microsoft SQL Server but it isn't stanard SQL and isn't supported by all other DBMSs. In ISO standard SQL there is no such thing as an index and FOREIGN KEY constraints are always required to match UNIQUE or PRIMARY KEY constraints. Personally I'd always prefer to use unique constraints rather than unique indexes without a constraint. I think a constraint makes the intended meaning clearer and the constraint syntax is more likely to be understood by another database developer.
dportas
2010-05-24 08:51:29
@David: yes, but the OP was asking about SQL Server.....
marc_s
2010-05-24 08:59:25
+6
A:
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).
dportas
2010-05-24 07:55:42
APC not sure why you want to edit this to replace "SQL" with "SQL Server". My comments apply to SQL (meaning the standard language defined by ISO). They apply to all implementations of SQL that I'm familiar with - not just SQL Server.
dportas
2010-05-24 13:22:53