How to Create a Foreign Key Constraint Referencing a Non-Primary Key Column?

Referring to non-primary keys in foreign key constraints

Maintaining referential integrity when linking tables via foreign keys is a common requirement. However, this situation occurs when the table referenced by the foreign key does not have a primary key corresponding to the referenced column.

Problem description:

Consider the following database schema:

<code class="language-sql">CREATE TABLE table1
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   SomeData VARCHAR(100) NOT NULL
);

CREATE TABLE table2
(
   ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
   AnotherID INT NOT NULL,
   MoreData VARCHAR(30) NOT NULL,

   CONSTRAINT fk_table2_table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID)
);</code>

The goal here is to enforce a foreign key constraint between table2.AnotherID and table1.AnotherID. However, ID is the primary key of table1 and AnotherID is not.

Solution:

To create a foreign key constraint that references a non-primary key, the referenced column must have a unique constraint applied to it. According to Microsoft’s Books Online:

"A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined as a column that references a UNIQUE constraint in another table."

So, in the given schema, applying a unique constraint to AnotherID in table1 will allow the creation of a foreign key constraint.

<code class="language-sql">ALTER TABLE table1 ADD CONSTRAINT UQ_AnotherID UNIQUE (AnotherID);</code>

However, it is important to note that if an alternative primary key candidate exists, using this is often the more appropriate approach.

The above is the detailed content of How to Create a Foreign Key Constraint Referencing a Non-Primary Key Column?. For more information, please follow other related articles on the PHP Chinese website!