How to Resolve Foreign Key Constraint Errors in SQL Server When Creating Tables?

Creating Foreign Keys in SQL Server

Database management involves creating interconnections between tables to maintain data integrity. One way to achieve this is by using foreign keys. SQL Server employs a specific syntax for declaring foreign key constraints, distinguishing it from other database systems like PostgreSQL.

Syntax Issues and Resolution

The provided SQL script aims to create three tables: exams, question_bank, and anwser_bank, with the question_bank table referring to the exams table using a foreign key constraint. However, upon execution, an error occurs:

Msg 8139, Level 16, State 0, Line 9
Number of referencing columns in
foreign key differs from number of
referenced columns, table
'question_bank'.

The Error Explained

This error indicates a mismatch between the number of columns referenced in the foreign key declaration and the number of columns in the referenced table. In the provided script, the foreign key constraint in table question_bank references the exam_id column in exams, but the referenced table does not have a column named exam_id.

Correcting the Foreign Key Constraint

To resolve the issue, the foreign key declaration must be updated to reference the correct column, which is exam_id in table exams.

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null, -- References "exam_id" in table "exams"
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint FK_question_bank_exam_id foreign key references exams(exam_id)
);

Alternative Syntax

If desired, the foreign key constraint can be created using the ALTER TABLE statement, which offers the flexibility to add the constraint after the table has been created:

alter table question_bank
add constraint FK_question_bank_exam_id FOREIGN KEY ( question_exam_id ) references exams(exam_id)

This approach allows for more granular control over constraint naming and timing of creation.

The above is the detailed content of How to Resolve Foreign Key Constraint Errors in SQL Server When Creating Tables?. For more information, please follow other related articles on the PHP Chinese website!