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

Details

What is a FOREIGN KEY?

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

Rules for FOREIGN KEY

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

How Foreign Key Works

In this tutorial, you will learn

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:

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

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:

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:

 

YOU MIGHT LIKE: