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

Use foreign keys to maintain referential integrity of non-primary key columns

In database systems, foreign keys are used to establish relationships between tables and enforce referential integrity. Typically, foreign keys reference primary keys in other tables to ensure consistent data in dependent tables. However, in some cases, it may be necessary to create foreign keys pointing to non-primary key columns.

Let's consider the scenario described in the question, where one table (table2) needs to maintain referential integrity with another table (table1), but the column referenced in table1 is not a primary key. The sample SQL code provided demonstrates the table and foreign key definitions you are trying to create:

<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 problem is that the AnotherID column in table1 is not a primary key. In most database systems, foreign keys can only reference primary keys or columns with unique constraints.

To resolve this issue, you must ensure that the referenced column (AnotherID in table1) has a unique constraint defined. Unique constraints are enforced on a per-row basis, ensuring that the value in the column is unique across all rows of the table.

By adding a unique constraint on AnotherID in table1, it becomes a candidate for a foreign key reference. The following modified SQL code demonstrates this correction:

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

You can now successfully define a foreign key constraint in table2, enforcing referential integrity between the two tables.

<code class="language-sql">ALTER TABLE table2
ADD CONSTRAINT FK_Table2_Table1 FOREIGN KEY (AnotherID) REFERENCES table1 (AnotherID)</code>

However, as the answer to the original question points out, it is generally recommended to use the primary key as a candidate key. Primary keys are automatically defined as unique, requiring no additional constraints and simplifying foreign key definitions.

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