How to Correctly Create Foreign Keys in SQL Server to Avoid Column Mismatch Errors?

Creating Foreign Keys in SQL Server

When managing relational databases in SQL Server, creating foreign keys can be crucial for ensuring data integrity. Unlike other SQL platforms, SQL Server requires a slightly different approach.

In the provided SQL code, you are attempting to create a foreign key constraint in the question_bank table, referencing the exam_id column in the exams table. However, the error you encounter indicates a discrepancy in the number of referencing and referenced columns.

To rectify this issue, ensure that the columns in the foreign key relationship match exactly. In your code, the question_exam_id column in question_bank should be referencing only the exam_id column in exams.

Revised Code:

create table exams
(
    exam_id uniqueidentifier primary key,
    exam_name varchar(50),
);
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 (question_exam_id) references exams(exam_id)
);

By aligning the columns in the foreign key relationship, you can create a valid constraint that maintains referential integrity between the question_bank and exams tables.

The above is the detailed content of How to Correctly Create Foreign Keys in SQL Server to Avoid Column Mismatch Errors?. For more information, please follow other related articles on the PHP Chinese website!