SQL FOREIGN KEY: How to Create in SQL Server with Example
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
- 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
How to Create FOREIGN KEY in SQL
We can Create a Foreign Key in SQL server in 2 ways:
- SQL Server Management Studio
- 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
- 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
- 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