SQL Server FOREIGN KEY: How to Create with Example

What is a FOREIGN KEY?

A foreign key provides a way of enforcing referential integrity within SQL Server. In simple words, a foreign key ensures that values in one table must be present in another table.

Rules for FOREIGN KEY

• NULL is allowed in a SQL foreign key.
• The table being referenced is called the parent table.
• The table with the foreign key is called the child table.
• The foreign key in the child table references the primary key in the parent table.
• This parent-child relationship enforces the rule known as “referential integrity.”

The diagram below summarizes all the points above for the foreign key.

How to Create FOREIGN KEY in SQL

You can create a foreign key in SQL Server in two ways:

• SQL Server Management Studio
• T-SQL

SQL Server Management Studio

Parent Table: Say we have an existing parent table named ‘Course.’ Course_ID and Course_name are two columns, with Course_Id as the primary key.

Child Table: We need to create the second table as a child table. ‘Course_ID’ and ‘Course_Strength’ are its two columns. However, ‘Course_ID’ shall be the foreign key.

Step 1) Right-click on Tables > New > Table…

Step 2) Enter two column names as ‘Course_ID’ and ‘Course_Strength.’ Right-click on the ‘Course_Id’ column, then click Relationship.

Step 3) In ‘Foreign Key Relationships,’ click ‘Add.’

Step 4) In ‘Tables and Columns Specification,’ click on the ‘…’ icon.

Step 5) Select the ‘Primary Key Table’ as ‘COURSE’ and the new table being created as the ‘Foreign Key Table’ from the drop-down.

Step 6) For the ‘Primary Key Table,’ select the ‘Course_Id’ column as the primary key table column.

For the ‘Foreign Key Table,’ select the ‘Course_Id’ column as the foreign key table column. Click OK.

Step 7) Click on Add.

Step 8) Give the table name as ‘Course_Strength’ and click OK.

Result: We have set a parent-child relationship between ‘Course’ and ‘Course_Strength.’

T-SQL: Create a Parent-Child Table Using T-SQL

Parent Table: Reconsider that we have an existing parent table with the name ‘Course.’ Course_ID and Course_name are two columns, with Course_Id as the primary key.

Child Table: We need to create the second table as the child table with the name ‘Course_Strength_TSQL.’ ‘Course_ID’ and ‘Course_Strength’ are its two columns. However, ‘Course_ID’ shall be the foreign key.

Below is the syntax to create a table with a FOREIGN KEY.

Syntax:

CREATE TABLE childTable
(
  column_1 datatype [ NULL |NOT NULL ],
  column_2 datatype [ NULL |NOT NULL ],
  ...

  CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n)
    [ ON DELETE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ]
    [ ON UPDATE { NO ACTION |CASCADE |SET NULL |SET DEFAULT } ] 
);

Here is a description of the above parameters:

• childTable is the name of the table that is to be created.
• column_1, column_2 are the columns to be added to the table.
• fkey_name is the name of the foreign key constraint to be created.
• child_column1, child_column2 … child_column_n are the child table columns that reference the primary key in parentTable.
• parentTable is the name of the parent table whose key is referenced in the child table.
• parent_column1, parent_column2 … parent_column_n are the columns making up the primary key of the parent table.
• ON DELETE is an optional parameter that specifies what happens to the child data after the parent data is deleted. Values include NO ACTION, SET NULL, CASCADE, or SET DEFAULT.
• ON UPDATE is an optional parameter that specifies what happens to the child data after the parent data is updated. Values include NO ACTION, SET NULL, CASCADE, or SET DEFAULT.
• NO ACTION means nothing happens to the child data after the parent data is updated or deleted.
• CASCADE means the child data is deleted or updated after the parent data has been deleted or updated.
• SET NULL means the child data is set to null after the parent data is updated or deleted.
• SET DEFAULT means the child data is set to its default value after an update or delete on the parent data.

Let us see a foreign key example that creates a table with one column as a FOREIGN KEY, using a data type for each column.

Foreign Key in SQL Example

Query:

CREATE TABLE Course_Strength_TSQL
(
Course_ID Int,
Course_Strength Varchar(20) 
CONSTRAINT FK FOREIGN KEY (Course_ID)
REFERENCES COURSE (Course_ID)	
)

Step 1) Run the query by clicking Execute.

Result: We have set a parent-child relationship between ‘Course’ and ‘Course_Strength_TSQL.’

Using ALTER TABLE

Now we will learn how to add a foreign key in SQL Server to a table that already exists using the ALTER TABLE statement. We will use the syntax given below:

ALTER TABLE childTable
ADD CONSTRAINT fkey_name
    FOREIGN KEY (child_column1, child_column2, ... child_column_n)
    REFERENCES parentTable (parent_column1, parent_column2, ... parent_column_n);

Here is a description of the parameters used above:

• childTable is the name of the table that is to be created.
• column_1, column_2 are the columns to be added to the table.
• fkey_name is the name of the foreign key constraint to be created.
• child_column1, child_column2 … child_column_n are the child table columns that reference the primary key in parentTable.
• parentTable is the name of the parent table whose key is referenced in the child table.
• parent_column1, parent_column2 … parent_column_n are the columns making up the primary key of the parent table.

ALTER TABLE add foreign key example:

ALTER TABLE department
ADD CONSTRAINT fkey_student_admission
    FOREIGN KEY (admission)
    REFERENCES students (admission);

We have created a foreign key named fkey_student_admission on the department table. This foreign key references the admission column of the students table.

Example Query FOREIGN KEY

First, let us see our parent table data, COURSE.

Query:

SELECT * from COURSE;

Now let us insert some rows in the child table ‘Course_Strength_TSQL.’ We will try to insert two types of rows:

• The first type, for which Course_Id in the child table exists in Course_Id of the parent table, that is, Course_Id = 1 and 2.
• The second type, for which Course_Id in the child table does not exist in the Course_Id of the parent table, that is, Course_Id = 5.

Query:

Insert into COURSE_STRENGTH values (1,'SQL');
Insert into COURSE_STRENGTH values (2,'Python');
Insert into COURSE_STRENGTH values (5,'PERL');

Result: Let us run the query together to see our parent and child tables.

The rows with Course_ID 1 and 2 exist in the Course_Strength table. Course_ID 5, however, is an exception, because it has no matching row in the parent table.

FAQs