Why Is My SQL Server Foreign Key Constraint Failing, and How Can I Fix It?

Troubleshooting Foreign Key Creation in SQL Server

When defining foreign keys in SQL Server, care must be taken to ensure the syntax is correct. Foreign keys establish referential integrity between tables, ensuring the consistency and validity of data.

In the provided code, an error is encountered while creating a foreign key constraint on the question_bank table referencing the exam_id column in the exams table. The error message indicates a mismatch in the number of referencing columns and referenced columns.

The issue stems from the absence of a column in the question_bank table that corresponds to the exam_id column in the exams table. To establish a valid foreign key relationship, there must be a match between the referencing and referenced columns.

To resolve this error, add a column named question_exam_id to the question_bank table. This column should have the same data type as the exam_id column in the exams table.

The corrected code now creates the foreign key constraint successfully:

...
create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null, -- Added this line
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint question_exam_id foreign key references exams(exam_id)
);
...

Additionally, if the constraint needs to be created independently, the ALTER TABLE statement can be used:

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

By ensuring the proper alignment of referencing and referenced columns, foreign keys can be effectively established in SQL Server, safeguarding data integrity and preventing inconsistencies.

The above is the detailed content of Why Is My SQL Server Foreign Key Constraint Failing, and How Can I Fix It?. For more information, please follow other related articles on the PHP Chinese website!