When Should You Use a Foreign Key to a Non-Primary Key Column?

Foreign Key Constraints and Non-Primary Key Columns

Relational databases utilize foreign key constraints to uphold referential integrity between tables. While foreign keys usually reference primary keys, there are instances where referencing a non-primary key column is necessary.

Scenario Illustration

Let's examine two tables, table1 and table2:

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

Here, a foreign key relationship is desired between table2 and table1 using the AnotherID column, which isn't a primary key in table1.

Establishing a Foreign Key to a Non-Primary Key

Although less frequent, creating a foreign key referencing a non-primary key column is feasible. However, a unique constraint must be enforced on the non-primary key column:

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

This unique constraint guarantees the uniqueness of AnotherID values within table1, enabling its use as a foreign key target.

Recommended Alternative

Ideally, foreign keys should reference primary keys for stronger referential integrity. If possible, redesign your database schema to utilize table1's ID column as the foreign key target in table2. This eliminates the need for managing unique constraints on non-primary key columns.

The above is the detailed content of When Should You Use a Foreign Key to a Non-Primary Key Column?. For more information, please follow other related articles on the PHP Chinese website!