Foreign key in MS SQL Server

A foreign key in SQL Server plays a crucial role in establishing and enforcing relationships between tables. It is a column or a set of columns in a table that references the primary key or a unique key in another table.

By using foreign key constraints the SQL Server keeps data consistent between related tables. In this article, We will learn about Foreign keys in MS SQL Server in detail by understanding various examples and so on.

What is a Foreign key in SQL Server?

• A foreign key in SQL Server is a column or a set of columns in one table that creates a relationship with the data in another table.
• It refers to the primary key or a unique key of another table, enforcing referential integrity between the two tables.

Key Features of a Foreign Key

• It ensures that the value in the foreign key column corresponds to an existing value in the primary key column of the referenced table.
• It prevents actions that would destroy the links between tables, such as deleting a referenced row in the parent table.
• We can define foreign keys to enforce cascading updates or deletes to maintain data consistency.

Syntax:

ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column)
REFERENCES parent_table (parent_column);

Explanation:

• ALTER TABLE child_table: This command modifies the existing table (child_table) to add a foreign key constraint.
• ADD CONSTRAINT fk_name: CONSTRAINT is used to define a name (fk_name) for the foreign key constraint. Giving the foreign key a meaningful name is good practice for easy identification.
• FOREIGN KEY (child_column): It Specifies the column (or columns) in the child table (child_table) that will store the foreign key. This column contains values that correspond to the primary or unique key in the referenced (parent) table.
• REFERENCES parent_table (parent_column): It Indicates the table (parent_table) and the column (parent_column) in the parent table that the foreign key refers to. The values in the foreign key column must match values in this referenced column, which is typically a primary or unique key.

Table: student

Table: Marks

Creating a Foreign Key

• In the given tables, student and marks, the Rollno column in both tables can be used to establish a relationship because it is common to both.
• We can create a foreign key in the marks table, linking the Rollno column in marks to the Rollno column in the student table.
• This ensures that every Rollno in the marks table corresponds to a valid Rollno in the student table.

ALTER TABLE Marks
ADD CONSTRAINT FK_StudentMarks
FOREIGN KEY (Rollno)
REFERENCES Student(Rollno);

Explanation:

• ALTER TABLE Marks: We are modifying the Marks table.
• ADD CONSTRAINT FK_StudentMarks: We are adding a foreign key constraint named FK_StudentMarks.
• FOREIGN KEY (Rollno): The foreign key is on the Rollno column in the Marks table.
• REFERENCES Student(Rollno): It references the Rollno column in the Student table.

This ensures that the Rollno values in the Marks table must match the Rollno values in the Student table, maintaining referential integrity between the two tables.

Foreign Key Update and Delete Rules

When defining a foreign key, we can also specify update and delete rules to control what happens when a referenced row in the parent table (student) is updated or deleted.

1. ON UPDATE CASCADE:

• If we set this rule, any update to the Rollno in the parent table (student) will be automatically reflected in the child table (marks).
• This ensures that if the Rollno in the student table is modified, it gets updated in marks as well.

Example:

ALTER TABLE Marks
ADD CONSTRAINT FK_StudentMarks
FOREIGN KEY (Rollno)
REFERENCES Student(Rollno)
ON UPDATE CASCADE;

Explanation:

This query modifies the `Marks` table by adding a foreign key constraint on the `Rollno` column, linking it to the `Rollno` column in the `Student` table. The `ON UPDATE CASCADE` ensures that if the `Rollno` in the `Student` table is updated, the corresponding `Rollno` in the `Marks` table is automatically updated, maintaining data consistency between the two tables.

2. ON DELETE CASCADE:

• With this rule, if a row in the student table is deleted, any corresponding rows in the marks table will also be deleted.
• This ensures that if a student is removed from the student table, their associated marks will also be removed from the marks table, avoiding irrelevant records.

Example:

ALTER TABLE Marks
ADD CONSTRAINT FK_StudentMarks
FOREIGN KEY (Rollno)
REFERENCES Student(Rollno)
ON DELETE CASCADE;

Explanation: This query modifies the `Marks` table by adding a foreign key constraint on the `Rollno` column, linking it to the `Rollno` column in the `Student` table. The `ON DELETE CASCADE` rule ensures that if a record in the `Student` table is deleted, all corresponding records in the `Marks` table (based on `Rollno`) will be automatically deleted, preserving referential integrity.

Other Rules:

• ON UPDATE SET NULL / ON DELETE SET NULL: When this rule is applied, if the parent record is updated or deleted, the corresponding foreign key value in the child table will be set to NULL rather than being updated or deleted. This helps preserve the child record, but it removes the link between the child and parent by setting the foreign key to NULL.
• ON UPDATE NO ACTION / ON DELETE NO ACTION: This is the default behavior. It prevents changes to the parent record (either update or delete) if it would violate the foreign key constraint.

Conclusion

Foreign keys are essential for maintaining referential integrity and consistency in SQL Server databases. By defining foreign key relationships and specifying update and delete rules, database administrators can ensure that changes in parent tables are appropriately reflected in child tables. These constraints help avoid orphaned records, ensure data integrity, and support efficient database design.