In the previous article, we discussed the concept of primary keys (PK) in SQL Server. In this article, we will delve into the concept of foreign keys (FK) and explore how they are related to primary keys. Understanding foreign keys is crucial for building a solid foundation in database theory.
What is a Foreign Key?
A foreign key is an attribute, or a combination of attributes, in a table that is directly related to the primary key of another table. It establishes a relationship between two tables, where the attribute in one table (referred to as the child table) is linked to the primary key attribute in another table (referred to as the referenced table). The foreign key ensures that the value stored in the child table attribute must always be a valid value from the referenced table’s primary key.
For example, let’s consider two tables: “country” and “city”. Both tables have an attribute called “id” as their primary key. In the “city” table, there is an additional attribute called “country_id” which serves as the foreign key. This foreign key establishes a relationship between the “city” table and the “country” table, indicating that the value stored in the “country_id” attribute must be a valid value from the “id” attribute in the “country” table.
How are Foreign Keys Stored/Defined in the Database?
Foreign keys are stored and defined in the database using specific constraints. When creating a foreign key, you can specify various properties such as replication, enforce foreign key constraint, delete rule, and update rule.
For example, when defining a foreign key relationship between the “city” and “country” tables, you can set the delete rule to “No Action”. This means that if you try to delete a row from the “country” table that has related records in the “city” table, the deletion will be prevented to maintain referential integrity.
Here is an example of how a foreign key is defined in SQL Server:
ALTER TABLE [dbo].[city] ADD CONSTRAINT [city_country] FOREIGN KEY ([country_id]) REFERENCES [dbo].[country] ([id])
What Does a Foreign Key Actually Do?
A foreign key performs several important checks to ensure data integrity:
- When adding a row to a table, the attribute(s) included in the foreign key must have a corresponding value in the referenced table. This ensures that the relationship between the two tables is maintained.
- If a value is added to the foreign key attribute that does not exist in the referenced table, an error will be thrown, preventing the insertion of inconsistent data.
- When deleting data from the referenced table, the foreign key constraint prevents the deletion if there are related records in the child table. This ensures that data integrity is maintained and avoids orphaned records.
For example, if you try to delete a country from the “country” table that still has cities associated with it, the foreign key constraint will prevent the deletion to avoid leaving orphaned cities without a valid country.
Conclusion
Foreign keys play a crucial role in establishing relationships between tables in a SQL Server database. They ensure data integrity and maintain referential integrity between related tables. Understanding foreign keys is essential for designing a well-structured and reliable database.
In future articles, we will explore more advanced topics related to foreign keys, such as different types of relationships, referential integrity, and indices.