Why Does My SQL Server Foreign Key Constraint Fail with a 'Number of Referencing Columns' Error?

Resolving Foreign Key Creation Issues in SQL Server

In SQL Server, foreign key declarations differ from those in PostgreSQL. Let's examine a scenario where a query resulted in the error: "Number of referencing columns in foreign key differs from number of referenced columns, table 'question_bank'."

Cause of the Error:

The SQL code provided attempted to create the following tables:

CREATE TABLE question_bank (
    question_id UNIQUEIDENTIFIER PRIMARY KEY,
    question_exam_id UNIQUEIDENTIFIER NOT NULL,
    question_text VARCHAR(1024) NOT NULL,
    question_point_value DECIMAL,
    CONSTRAINT question_exam_id FOREIGN KEY REFERENCES exams(exam_id)
);

The error occurred because the foreign key constraint "question_exam_id" in the "question_bank" table references the "exam_id" column in the "exams" table. However, only one referencing column ("question_exam_id") is present in the "question_bank" table, while the "exams" table has multiple columns.

Solution:

To resolve this issue, we need to ensure that the number of referencing columns matches the number of referenced columns. In this case, we only need to add the "exam_name" column from the "exams" table to the "question_bank" table:

ALTER TABLE question_bank ADD COLUMN exam_name VARCHAR(50);

Now, the foreign key constraint can be created successfully:

ALTER TABLE question_bank
ADD CONSTRAINT question_exam_id FOREIGN KEY (question_exam_id, exam_name) REFERENCES exams(exam_id, exam_name);

Alternative Syntax:

Alternatively, you can create the constraint during the table creation process:

CREATE TABLE question_bank (
    question_id UNIQUEIDENTIFIER PRIMARY KEY,
    question_exam_id UNIQUEIDENTIFIER NOT NULL,
    exam_name VARCHAR(50),
    question_text VARCHAR(1024) NOT NULL,
    question_point_value DECIMAL,
    CONSTRAINT question_exam_id FOREIGN KEY (question_exam_id, exam_name) REFERENCES exams(exam_id, exam_name)
);

In this case, the "exam_name" column is added to the "question_bank" table and the foreign key constraint is created simultaneously during table creation.

The above is the detailed content of Why Does My SQL Server Foreign Key Constraint Fail with a 'Number of Referencing Columns' Error?. For more information, please follow other related articles on the PHP Chinese website!