SQL with Manoj

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables, as per MSDN BoL.
 

–> Foreign Key (FK) Constraint

1. We can create a FK by defining a FOREIGN KEY constraint while Creating or modifying (Altering) a table.

2. We can define a FK in a table that points to a PK or UK in another table.
   – Like Employee & Department, shown in video-demo below.

3. A FK constraint can reference columns within the same table, these are called self-referencing tables. For example: Employee & Manager Relationship.
 

–> Referential Integrity:

1. The FK constraint enforces Referential Integrity by not allowing values in the Child table outside form the domain of the Parent table.

2. This also disallows any changes to the Parent table, like deleting any row or modifying PK value in the Parent table.
 

–> Check the video on Foreign Key:

–> Department and Employee table FK relationship:

–> SQL Code used in above video:

-- 1. Parent table:

CREATE TABLE [dbo].[Department](
	 [DeptID]	int IDENTITY (101, 1) PRIMARY KEY
	,[DeptName]	nvarchar(100)
	,[isActive]	bit DEFAULT(1)
)
GO

INSERT INTO [dbo].[Department] ([DeptName])
SELECT 'HR'
UNION ALL
SELECT 'Finance'
UNION ALL
SELECT 'Admin'
UNION ALL
SELECT 'IT'

select * from [dbo].[Department]
GO


-- 2. Child Table:

-- Method #1.a : with Column inline
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL FOREIGN KEY REFERENCES Department(DeptID)
	,[isActive]		bit DEFAULT(1)
)
GO

-- Method #1.b : with Column inline & Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY REFERENCES Department(DeptID)
	,[isActive]		bit DEFAULT(1)
)
GO

-- Method #2.a : with Explicit
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
		FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
)
GO

-- Method #2.b : with Explicit & Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
		CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
)
GO

-- Method #3 : with ALTER Table Named FK
DROP TABLE [dbo].[Employee]
GO
CREATE TABLE [dbo].[Employee](
	 [EmployeeID]	int IDENTITY (100, 1) PRIMARY KEY
	,[EmployeeName]	nvarchar(100)
	,[Gender]		nchar(1)
	,[DeptID]		int NOT NULL
	,[isActive]		bit DEFAULT(1)
)
GO

-- SSMS or ALTER Table stmt:

-- Generated by SSMS - Table Designer:
ALTER TABLE dbo.Employee ADD CONSTRAINT
	FK_Employee_Department FOREIGN KEY
	(
	DeptID
	) REFERENCES dbo.Department
	(
	DeptID
	)

-- OR --

ALTER TABLE [dbo].[Employee]
ADD CONSTRAINT FK_EmployeeID_DeptID FOREIGN KEY ([DeptID]) REFERENCES Department(DeptID)
GO


INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Manoj Pandey', 'M', 101)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Deepak B', 'M', 102)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Jhon B', 'M', 103)

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Mariya Y', 'F', 104)

select * from [dbo].[Employee]
GO


-- REFERENTIAL INTEGRITY:

INSERT INTO Employee ([EmployeeName], [Gender], [DeptID])
VALUES ('Mary J', 'F', 105)
--Msg 547, Level 16, State 0, Line 115
--The INSERT statement conflicted with the FOREIGN KEY constraint "FK_EmployeeID_DeptID". The conflict occurred in database "TestManDB", table "dbo.Department", column 'DeptID'.
--The statement has been terminated.

DELETE FROM [dbo].[Department]
WHERE DeptID = 102
--Msg 547, Level 16, State 0, Line 118
--The DELETE statement conflicted with the REFERENCE constraint "FK_EmployeeID_DeptID". The conflict occurred in database "TestManDB", table "dbo.Employee", column 'DeptID'.
--The statement has been terminated.

DROP TABLE [dbo].[Department]
--Msg 3726, Level 16, State 1, Line 121
--Could not drop object 'dbo.Department' because it is referenced by a FOREIGN KEY constraint.

GO


-- Allowed updates:

select * from [dbo].[Department]

UPDATE [dbo].[Department]
SET DeptName = 'Human Resource'
WHERE DeptID = 101

select * from [dbo].[Department]

GO


-- Final Cleanup:

DROP TABLE [dbo].[Employee]
GO
DROP TABLE [dbo].[Department]
GO

Check Primary Key & Unique Key constraints

To know about more on Constraints and their types check this blog post.