SQL FOREIGN KEY: How to Create in SQL Server with Example

Rules for FOREIGN KEY

• NULL is allowed in SQL Foreign key.
• The table being referenced is called the Parent Table
• The table with the Foreign Key in SQL is called Child Table.
• The SQL Foreign Key in child table references the primary key in the parent table.
• This parent-child relationship enforces the rule which is known as “Referential Integrity.”

The Below Foreign Key in SQL example with diagram summarizes all the above points for FOREIGN KEY

In this tutorial, you will learn

• How to Create FOREIGN KEY in SQL
• Using SQL Server Management Studio
• Using T-SQL: Create Child Table
• Using ALTER TABLE
• Example Query FOREIGN KEY

How to Create FOREIGN KEY in SQL

We can Create a Foreign Key in SQL server in 2 ways:

1. SQL Server Management Studio
2. T-SQL

SQL Server Management Studio

Parent Table: Say, we have an existing Parent table as ‘Course.’ Course_ID and Course_name are two columns with Course_Id as Primary Key.

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

Step 1) Right Click on Tables>New> Table…

Step 2) Enter two column name as ‘Course_ID’ and ‘Course_Strength.’ Right click on ‘Course_Id’ Column. Now click on Relationship.

Step 3) In ‘Foreign Key Relationship,’ Click ‘Add’

Step 4) In ‘Table and Column Spec’ click on ‘…’ icon

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

Step 6) ‘Primary Key Table’ – Select ‘Course_Id’ column as ‘Primary Key table’ column.

‘Foreign Key Table’- Select ‘Course_Id’ column as ‘Foreign Key table’ column. Click OK.

Step 7) Click on Add.

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

Result: We have set Parent-child relationship between ‘Course’ and ‘Course_strength.’

T-SQL: Create a Parent-child table using T-SQL

Parent Table: Reconsider, we have an existing Parent table with table name as ‘Course.’

Course_ID and Course_name are two columns with Course_Id as Primary Key.

Child Table: We need to create the second table as the child table with the name as ‘Course_Strength_TSQL.’

‘Course_ID’ and ‘Course_Strength’ as two columns for child table Course_Strength_TSQL.’ However, ‘Course_ID’ shall be Foreign Key.

Below is the syntax to create a table with 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- the columns to be added to the table.
• fkey_name- the name of the foreign key constraint to be created.
• child_column1, child_column2…child_column_n- the name of chidTable columns to reference the primary key in parentTable.
• parentTable- the name of parent table whose key is to be referenced in the child table.
• parent_column1, parent_column2, … parent_column3- the columns making up the primary key of parent table.
• ON DELETE. An optional parameter. It specifies what happens to the child data after deletion of the parent data. Some of the values for this parameter include NO ACTION, SET NULL, CASCADE, or SET DEFAULT.
• ON UPDATE- An optional parameter. It specifies what happens to the child data after update on the parent data. Some of the values for this parameter include NO ACTION, SET NULL, CASCADE, or SET DEFAULT.
• NO ACTION- used together with ON DELETE and ON UPDATE. It means that nothing will happen to the child data after the update or deletion of the parent data.
• CASCADE- used together with ON DELETE and ON UPDATE. The child data will either be deleted or updated after the parent data has been deleted or updated.
• SET NULL- used together with ON DELETE and ON UPDATE. The child will be set to null after the parent data has been updated or deleted.
• SET DEFAULT- used together with ON DELETE and ON UPDATE. The child data will be set to default values after an update or delete on the parent data.

Let’s see a Foreign Key in SQL example to create a table with One Column as a FOREIGN KEY:

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 on execute.

Result: We have set Parent-child relationship between ‘Course’ and ‘Course_strength_TSQL.’

Using ALTER TABLE

Now we will learn how to use Foreign Key in SQL and add Foreign Key in SQL server 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- the columns to be added to the table.
• fkey_name- the name of the foreign key constraint to be created.
• child_column1, child_column2…child_column_n- the name of chidTable columns to reference the primary key in parentTable.
• parentTable- the name of parent table whose key is to be referenced in the child table.
• parent_column1, parent_column2, … parent_column3- the columns making up the primary key of 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’s see our Parent Table Data, COURSE.

Query:

SELECT * from COURSE;

Now let’s insert some row in Child table: ‘Course_strength_TSQL.’

We will try to insert two types of rows

1. The first type, for which Course_Id in child table will exist in Course_Id of Parent table. i.e. Course_Id = 1 and 2
2. The second type, for which Course_Id in child table doesn’t exist in the Course_Id of Parent table. i.e. 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’s run the Query together to See our Parent and Child table

Row with Course_ID 1 and 2 exist in Course_strength table. Whereas, Course_ID 5 is an exception.

Summary:

• Every value of Foreign key has to be part of Primary Key of other tables.
• The MySQL Foreign Key can reference to another column in the same table. This reference is known as a self-reference.
• SQL Foreign Key Constraint : is used to secure the links between tables and invalid data to be inserted into the Foreign Key column.
• You can create a Foreign Key using Create Table, Alter Table, or SQL Server Management Studio.
• Here is the difference between Primary Key vs Foreign Key: Click Here