views:

134

answers:

2

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

+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
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
@David: yes, but the OP was asking about SQL Server.....
marc_s
+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
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