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

Create a foreign key pointing to a non-primary key

Question:

How to maintain referential integrity by creating a foreign key pointing to a non-primary key column in another table?

Answer:

While it is generally recommended to reference a primary key when creating a foreign key, under certain conditions it is possible to create a foreign key that points to a non-primary key.

Solution:

To create a foreign key pointing to a non-primary key, a unique constraint must be applied to the target column in the referenced table. This means that the values ​​in the column must be unique to ensure data integrity.

Example:

Consider the following table structure:

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

In this example, the AnotherID column in table1 is not a primary key. However, it has a unique constraint that allows us to create a foreign key relationship between table2 and table1 using the AnotherID column.

Alternative:

If you cannot create a unique constraint on a non-primary key column, consider using the referenced table's primary key as the foreign key instead. This method ensures stronger database integrity and is generally recommended.

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