SQL Server - Foreign Key Relationships

To establish a connection between two or more tables, foreign key constraint will be used. One table's primary key column can be referred to another table's column as foreign key. It is to enforce a constraint to match the foreign key column data-type and values with parent key column data-type and values. The foreign key columns are used in joins while retrieving the data as the relationship between the tables indicates that the tables have been optimized to be combined.

There is no specific limitation to have number of foreign key constraints in a table, however, it is recommended to have not more than 253 foreign key constraints in a table. We can create a foreign key by defining REFERENCES or FOREIGN KEY constraint while creating or modifying a table.

Look into the following examples.

-------------
METHOD-1
-------------
CREATE TABLE studentGroups(
GroupID BIGINT PRIMARY KEY,
Groups VARCHAR(20))

CREATE TABLE student_details(
stdID BIGINT PRIMARY KEY,
student_Name VARCHAR(40) NOT NULL,
GroupID BIGINT REFERENCES studentGroups(GroupID))

-------------
METHOD-2
---------------
CREATE TABLE studentGroups(
GroupID BIGINT PRIMARY KEY,
Groups VARCHAR(20))

CREATE TABLE student_details(
stdID BIGINT PRIMARY KEY,
student_Name VARCHAR(40) NOT NULL,
GroupID BIGINT,
CONSTRAINT fk_group FOREIGN KEY(GroupID) REFERENCES studentGroups(GroupID))

------------------------------------------------------------
METHOD-3 -- ADDING FOREIGN KEY CONSTRAINT ON EXISTING TABLE
------------------------------------------------------------
CREATE TABLE studentGroups(
GroupID BIGINT PRIMARY KEY,
Groups VARCHAR(20))

CREATE TABLE student_details(
stdID BIGINT PRIMARY KEY,
student_Name VARCHAR(40) NOT NULL,
GroupID BIGINT)

ALTER TABLE student_details

WITH CHECK ADD CONSTRAINT fk_group FOREIGN KEY(GroupID)
REFERENCES studentGroups(GroupID)