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

  • SQL Server 2005 Fail: Return Dates As Strings
  • SQL Server FILESTREAM limitation
  • How to merge two row and sum some columns in an UPDATE?
  • status labeling for transactions
  • Error in the query “Invalid length parameter passed to the LEFT or SUBSTRING function.”
  • Drop Foreign Key without knowing the name of the constraint?
  • What are locking issues in OLAP?
  • How to optimise SQL table for read speed for point to point distance data
  • No nvarchar: “Operand data type nvarchar is invalid for sum operator”
  • Storing data in a side table that may change in its main table
  • How can you represent inheritance in a database?
  • SQL-Server DB design time scenario (distributed or centralized)
  • 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.