Primary and Foreign Key Constraints in SQL - Explained

Key Constraints in SQL play a vital role in maintaining data accuracy and integrity in relational databases. They prevent duplicate, NULL, or mismatched values, ensuring that data remains consistent and relationships between tables are properly maintained. Further read this blog to understand Key Constraints in SQL, their types, and how you find them in SQL.   

Table of contents 

1) What are Key Constraints in SQL? 

2) Primary Key Constraints 

3) Foreign Key Constraints 

4) How to find Foreign Key Constraints in an SQL Server? 

5) Conclusion 

What are Key Constraints in SQL?  

In SQL, primary and Foreign Keys are essential concepts that help establish relationships and maintain data integrity in relational databases. A Primary Key is a unique identifier for each record in a table, whereas a Foreign Key is a column(s) in one table that refers to the Primary Key of another table.   

By defining these constraints, Database Designers ensure that the data stored and manipulated within the database follows specific rules, leading to a well-organised and reliable database system.    

To master your database skills and learn SQL, Register for our Introduction To SQL Course! 

Primary Key Constraints 

A Primary Key Constraint is used to distinctively identify each record in a table. It ensures that the values in the specified column(s) are unique and cannot be NULL. The Primary Key serves as the main reference point for that table, and it helps establish relationships between tables through Foreign Key Constraints. Each table can have only one Primary Key Constraint.   

Here’s an example of a Primary Key Constraint to create a table named "Students". Use the SQL command "CREATE TABLE Students," followed by column definitions like "StudentID INT PRIMARY KEY" for the Primary Key column, "FirstName VARCHAR(50)" for the first name, and "LastName VARCHAR(50)" for the last name. The command establishes the structure for storing student information with a unique identifier. 

CREATE TABLE Students ( 

StudentID INT PRIMARY KEY, 

FirstName VARCHAR(50), 

LastName VARCHAR(50) 

); 

Register for our Advanced SQL course and take your SQL skills to the next level. 

Foreign Key Constraints 

SQL Foreign Key Constraint establishes a relationship between two tables by linking a column or set of columns in the table to the Primary Key in another table. It ensures that - values in the Foreign Key column(s) match the values in the referenced Primary Key column(s). This constraint is essential for maintaining referential integrity and data consistency across related tables.   

Here’s an example of a Foreign Key Constraint to create a table named "Orders," use "CREATE TABLE Orders," define columns: "OrderID INT PRIMARY KEY" for the Primary Key, "CustomerID INT" for customer reference, and "OrderDate DATE" for date. Then, use "FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)" to link CustomerID to the Customers table's Primary Key, ensuring data integrity between the two tables.   

CREATE TABLE Orders ( 

OrderID INT PRIMARY KEY, 

CustomerID INT, 

OrderDate DATE, 

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) 

); 
 

How to find Foreign Key Constraints in an SQL Server? 

SQL query retrieves information about Foreign Key Constraints from the system catalogue views in SQL Server. It lists details like the table name containing the Foreign Key, the column name of the Foreign Key, the referenced table name, the referenced column name, and the name of the Foreign Key Constraint. 

Here’s an example of how to find Foreign Key Constraints in SQL. This query retrieves Foreign Key Constraint information by joining the sys.foreign_keys and sys.foreign_key_columns views. It displays the table name and column name with the Foreign Key, along with the referenced table name and referenced column name.  

SELECT  

OBJECT_NAME (fkc.parent_object_id) AS TableName, 

COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ColumnName, 

OBJECT_NAME(fkc.referenced_object_id) AS ReferencedTableName, 

COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ReferencedColumnName, 

fk.name AS ForeignKeyName 

FROM sys.foreign_keys AS fk 

INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id; 

Conclusion 

Primary and Foreign Key Constraints are integral elements of relational databases. Their strategic implementation empowers businesses and organisations to harness the power of organised and connected data, ultimately leading to improved decision-making and efficient data handling. 

Register for our SQL Training and unlock the power of the database!