Introduction
In relational database architecture, a Foreign Key is an essential concept. It is a column (or a set of columns) in one table that uniquely identifies a row in another table. With the use of foreign keys, Data integrity is ensured and table connections are preserved which create and enforce a link between the data in two tables.
Key Points:
a. A primary key (or a unique key) in one table is referenced by a foreign key in another table.
b. By requiring that the value of a foreign key column either match one of the values in the referenced primary key column or be NULL, it helps in the maintenance of referential integrity.
1. Basic Structure of a Foreign Key
Parent and Child Tables:
a. Parent Table: This table contains the primary key that is referenced by the foreign key.
b. Child Table: This table contains the foreign key that refers to the primary key of the parent table.
Example:
Let’s assume we have two tables: Customers (parent) and Orders (child).
- Customers Table (Parent Table):
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName NVARCHAR(100),
City NVARCHAR(100)
);
- Orders Table (Child Table):
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT, -- Foreign key column referencing Customers table
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Explaination:
a. CustomerID in the Orders table is a foreign key that references CustomerID in the Customers table.
b. This enforces that every CustomerID in the Orders table must either be NULL or must already exist in the Customers table.
From Queries to Expertise—Join Our Microsoft SQL Server course at TrainingHub.io!
2. How to Define a Foreign Key in SQL Server
Using CREATE TABLE Statement:
When creating a table, you can define a foreign key in the table definition:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Using ALTER TABLE Statement:
You can also add a foreign key to an existing table using the ALTER TABLE statement:
ALTER TABLE Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);
• Here, FK_Orders_Customers is the name of the foreign key constraint.
3. Referential Integrity
3.1. Ensuring Referential Integrity
Referential integrity ensures that relationships between tables remain consistent. For example, you cannot have an order that references a non-existent customer.
3.2. Cascading Actions
Server provides cascading actions to maintain referential integrity automatically when changes happen in the parent table. These actions are:
• ON DELETE CASCADE: Deletes the corresponding rows in the child table when a row in the parent table is deleted.
• ON UPDATE CASCADE: Updates the corresponding rows in the child table when the primary key in the parent table is updated.
Example of Cascading Actions:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
• ON DELETE CASCADE: If a customer is deleted from the Customers table, their corresponding orders in the Orders table will also be deleted.
• ON UPDATE CASCADE: If the CustomerID in the Customers table is updated, the corresponding CustomerID in the Orders table will be automatically updated.
3.3. Other Referential Actions
• SET NULL: Sets the foreign key column to NULL when the referenced row is deleted/updated.
• SET DEFAULT: Sets the foreign key column to a default value when the referenced row is deleted/updated.
• NO ACTION: Prevents the deletion or update of a row in the parent table if there are matching rows in the child table.
Example:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
ON DELETE SET NULL
);
In this case, if a customer is deleted, the CustomerID in the Orders table will be set to NULL.
Get Ahead in Tech—Master Data Analytics program with TrainingHub.io!
4. Foreign Key Constraints and Enforcement
4.1. Violating Foreign Key Constraints
Server will reject any INSERT, UPDATE, or DELETE operation that violates a foreign key constraint. For example, attempting to insert an order with a CustomerID that does not exist in the Customers table will result in an error.
Example of a Violation:
INSERT INTO Orders (OrderID, OrderDate, CustomerID)
VALUES (105, '2024-07-01', 999); -- CustomerID 999 does not exist
This will generate an error because there is no customer with CustomerID = 999 in the Customers table.
4.2. Disabling Foreign Key Constraints
You can disable a foreign key constraint temporarily (e.g., during bulk inserts) and then re-enable it afterward.
• Disabling Foreign Key Constraint:
ALTER TABLE Orders NOCHECK CONSTRAINT FK_Orders_Customers;
• Re-enabling Foreign Key Constraint:
ALTER TABLE Orders CHECK CONSTRAINT FK_Orders_Customers;
Note: Disabling foreign keys can lead to data inconsistencies, so use it carefully.
4.3. Deferring Foreign Key Checks
In SQL Server, foreign key checks happen immediately during INSERT or UPDATE operations. Server does not natively support deferring foreign key checks until the end of a transaction, unlike some other RDBMS (like Oracle).
5. Viewing Foreign Keys in SQL Server
5.1. Querying Foreign Key Constraints
You can query system views to retrieve information about foreign keys.
SELECT
fk.name AS ForeignKey,
tp.name AS ParentTable,
cp.name AS ParentColumn,
tr.name AS ReferencedTable,
cr.name AS ReferencedColumn
FROM
sys.foreign_keys AS fk
JOIN
sys.tables AS tp ON fk.parent_object_id = tp.object_id
JOIN
sys.tables AS tr ON fk.referenced_object_id = tr.object_id
JOIN
sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
JOIN
sys.columns AS cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
JOIN
sys.columns AS cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id;
5.2. Viewing Foreign Keys in SQL Server Management Studio (SSMS)
In SSMS, you can visually inspect the Foreign Keys.
• Right-click on the table > Select Design > Click on Relationships to see and manage foreign keys.
6. Benefits of Using Foreign Keys
• Avoids orphaned records and ensures that the relationship between the tables is consistent [i.e. Data Integrity].
• Cascading options enable the automated updating or deletion of related data.
• Self-Documentation: The existence of foreign keys helps document the database schema and relationships between tables.
7. Drawbacks of Using Foreign Keys
• For large tables, enforcing foreign key constraints incurs performance Impact.
• Complexity: In large systems, complex foreign key relationships can make database management and migrations more difficult.
8. Best Practices
• Use Descriptive Names: Foreign keys and constraints should be named clear, e.g., FK_Orders_Customers.
• Limit Cascading Actions: Use cascading updates and deletes judiciously to avoid unintended data loss or corruption.
• Index Foreign Key Columns: Consider indexing foreign key columns to improve query performance, especially for joins.
9. Conclusion
A vital element of relational databases is a Foreign Key that ensure data integrity and support the safeguarding of key connections between the tables. Foreign key boundaries must be defined, enforced, and managed with the help of server's powerful capabilities, which are necessary to build dependable and consistent database systems.
You can prevent several typical database design errors and ensure your data is consistent and relationally sound by using foreign keys effectively.
Turn Queries into Career Opportunities—Explore Microsoft SQL Server Training at TrainingHub.io!
Suggested blogs
Recent blogs
