can we have a foreign key which is not a primary key in any other table?

Question

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

Answer 1

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) 

Answer 2

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).