How to implement primary key and Foreign key practically

 

Implementing primary keys and foreign keys in Microsoft SQL Server is a fundamental aspect of database design to ensure data integrity and maintain proper relationships between tables. Here's a practical guide on how to implement primary keys and foreign keys:

Implementing Primary Key:

A primary key uniquely identifies each record in a table. It ensures that no two rows can have the same values in the primary key column(s).

1. Create a Table: To create a table with a primary key, use the CREATE TABLE statement. For example:

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName NVARCHAR(50),
       LastName NVARCHAR(50)
   );
   

   In this example, the EmployeeID column is designated as the primary key.

2. Adding a Primary Key Constraint: You can also add a primary key constraint to an existing table using an ALTER TABLE statement. For example:

   ALTER TABLE Employees
   ADD CONSTRAINT PK_Employees_EmployeeID PRIMARY KEY (EmployeeID);
   

Implementing Foreign Key:

A foreign key establishes a relationship between two tables by referring to the primary key in another table. It enforces referential integrity.

1. Create the Related Tables: You should have two tables, one with the primary key and the other with the foreign key.

   CREATE TABLE Departments (
       DepartmentID INT PRIMARY KEY,
       DepartmentName NVARCHAR(50)
   );
   
   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName NVARCHAR(50),
       LastName NVARCHAR(50),
       DepartmentID INT,
       FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
   );
   

   In the Employees table, the DepartmentID column is a foreign key referring to the DepartmentID in the Departments table.

2. Adding a Foreign Key Constraint: You can add a foreign key constraint when creating the table, as shown in the example above. Alternatively, you can add it using an ALTER TABLE statement:

   ALTER TABLE Employees
   ADD CONSTRAINT FK_Employees_Departments FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID);
   

3. Maintaining Referential Integrity: Once the foreign key constraint is in place, it will enforce referential integrity. This means you can't insert a value into the Employees table's DepartmentID column if it doesn't exist in the Departments table's DepartmentID column.

Practical Considerations:

• Always ensure that data types and lengths of columns match when creating primary and foreign key relationships.

• It's good practice to use meaningful names for primary and foreign keys to make your database schema more understandable.

• You can cascade actions like updates and deletes when dealing with foreign keys. For example, when a department is deleted, you can set up cascading delete to remove related employees automatically.

Implementing primary and foreign keys is crucial in maintaining data integrity, ensuring data consistency, and simplifying data relationships in your SQL Server database.