How to Establish Foreign Key Constraints in SQL Server?

Establishing Foreign Key Constraints in SQL Server

Maintaining data integrity is crucial in any database system. SQL Server provides the ability to enforce relationships between tables using foreign key constraints. However, declaring foreign keys differs from other database management systems like PostgreSQL.

Syntax and Considerations

Here's how to create a foreign key in SQL Server:

ALTER TABLE <child_table>
ADD CONSTRAINT <constraint_name>
FOREIGN KEY (<child_column>)
REFERENCES <parent_table>(<parent_column>)

Ensure that the number of referencing columns in the child table matches the number of referenced columns in the parent table. Otherwise, you'll encounter an error similar to:

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

Troubleshooting: Incorrect Syntax

The provided SQL code for creating the question_bank table has an error. The question_exam_id column in the question_bank table should reference the exam_id column in the exams table.

create table question_bank
(
    question_id uniqueidentifier primary key,
    question_exam_id uniqueidentifier not null, <!-- This should be a foreign key -->
    question_text varchar(1024) not null,
    question_point_value decimal,
    constraint question_exam_id foreign key references exams(exam_id)
);

Alternate Syntax: Adding Constraints Later

You can also create foreign key constraints after creating the child table using the ALTER TABLE statement:

alter table question_bank
add constraint question_exam_id_fk foreign key (question_exam_id) references exams(exam_id)

This method allows you to define constraints separately from table creation, providing greater flexibility and control over the database schema.

The above is the detailed content of How to Establish Foreign Key Constraints in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!