Understanding Foreign Keys in SQL Server

When designing a relational database, it’s essential to establish relationships between tables to maintain data integrity. One way to accomplish this is through the use of foreign keys. A foreign key in SQL Server is a field (or collection of fields) in one table that uniquely identifies a row of another table. If you’re coming from another database system, such as PostgreSQL, you may find some differences in syntax and implementation in SQL Server, which can lead to confusion.

The Problem: Creating a Foreign Key

In the given scenario, there is an attempt to create three tables: exams, question_bank, and answer_bank. The goal is to create a foreign key in question_bank that references the exams table. However, an error arises when running the SQL code. The key message from the error is:

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

This indicates that there is a mismatch in the foreign key definition.

Solution: Correctly Defining the Foreign Key

Step 1: Review the Table Definitions

Let’s take a closer look at how to correctly set up the foreign key in the question_bank table.

Current Table Setup

The original SQL created the question_bank table like this:

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 foreign key is intended to reference exam_id from the exams table, but it is lacking the appropriate syntax and structure.

Step 2: Update the Foreign Key Definition

To properly define the foreign key relationship, specify how the foreign key column (question_exam_id) relates to the primary key of the referenced table (exam_id):

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 fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);

Key Changes:

• Specify the foreign key name: It’s good practice to name the foreign key constraint (e.g., fk_questionbank_exams) for clarity.
• Correctly reference the foreign key column: Ensure that the foreign key references the correct column in the referenced table.

Step 3: Implementing the Changes

Now, you can drop the existing question_bank table (if it exists) and create it again with the corrected foreign key definition using:

drop table if exists question_bank;

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 fk_questionbank_exams foreign key (question_exam_id) references exams (exam_id)
);

Conclusion

Setting up foreign keys in SQL Server is a crucial step in ensuring data integrity and establishing relationships between tables. By carefully defining the foreign key constraints and correcting any syntax issues, you can create a robust and efficient database design.

If you encounter any errors, always check the foreign key definitions to ensure that the referencing column correctly corresponds with the referenced column. With this guide, you should be well-equipped to implement foreign keys successfully in your SQL Server databases.