---
description: This tutorial covers the definition of a foreign key, rules, how the foreign key works, How to create a foreign key with SQL server management studio, and more.
title: SQL Server FOREIGN KEY: How to Create with Example
image: https://www.guru99.com/images/sql-server-foreign-key-1.png
---

 

[Skip to content](#main) 

**⚡ Smart Summary**

A foreign key in SQL Server enforces referential integrity by linking a child table to a parent table. Every foreign key value must already exist in the referenced primary key of the parent table.

* 🔗 **What is a foreign key:** A foreign key links a child table to a parent table and enforces referential integrity between them.
* 👪 **Parent and child:** The referenced table is the parent; the table holding the foreign key is the child, which points to the parent primary key.
* 🖱️ **Two creation methods:** SQL Server Management Studio relationships and the T-SQL CREATE TABLE … FOREIGN KEY … REFERENCES clause both define a foreign key.
* ➕ **Add to an existing table:** ALTER TABLE … ADD CONSTRAINT … FOREIGN KEY adds the relationship to a table that already exists.
* 🔄 **Referential actions:** ON DELETE and ON UPDATE clauses control child rows with NO ACTION, CASCADE, SET NULL, or SET DEFAULT.
* ✅ **Integrity check:** Inserting a child row whose key has no matching parent row is rejected, keeping the data consistent.

[ Read More ](javascript:void%280%29;) 

![SQL Server FOREIGN KEY: How to Create in SQL Server with an Example](https://www.guru99.com/images/sql-server-foreign-key-1.png)

## What is a FOREIGN KEY?

A foreign key provides a way of enforcing referential integrity within [SQL Server](https://www.guru99.com/ms-sql-server-tutorial.html). 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](https://www.guru99.com/sql-server-primary-key.html) 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.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO1.png)

## How to Create FOREIGN KEY in SQL

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

* [SQL Server Management Studio](https://www.guru99.com/sql-server-management-studio.html)
* 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.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO2.png)

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…

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO3.png)

Step 2) Enter two column names as ‘Course\_ID’ and ‘Course\_Strength.’ Right-click on the ‘Course\_Id’ column, then click Relationship.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO4.png)

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

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO5.png)

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

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO6.png)

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

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO7.png)

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.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO8.png)

Step 7) Click on Add.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO9.png)

Step 8) Give the table name as ‘Course\_Strength’ and click OK.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO10.png)

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

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO11.png)

### RELATED ARTICLES

* [Oracle vs SQL Server – Difference Between Them ](https://www.guru99.com/oracle-vs-sql-server.html "Oracle vs SQL Server – Difference Between Them")
* [SSAS Tutorial: What is SSAS Cube, Architecture & Types ](https://www.guru99.com/ssas-tutorial.html "SSAS Tutorial: What is SSAS Cube, Architecture & Types")
* [CASE Statement & Nested Case in SQL Server: T-SQL Example ](https://www.guru99.com/sql-server-case.html "CASE Statement & Nested Case in SQL Server: T-SQL Example")
* [SQL Server Tutorial PDF for Beginners (Free Download) ](https://www.guru99.com/sql-server-tutorial-pdf.html "SQL Server Tutorial PDF for Beginners (Free Download)")

### 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.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO12.png)

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](https://www.guru99.com/sql-server-table-create-alter-drop.html) 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](https://www.guru99.com/sql-server-datatype.html) 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.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO13.png)

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

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO14.png)

## 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;

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO15.png)

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');

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO16.png)

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.

[](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO17.png)

## FAQs

🔑 What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row inside its own table and cannot be NULL. A foreign key references that primary key from another table to enforce referential integrity. This [primary key versus foreign key](https://www.guru99.com/difference-between-primary-key-and-foreign-key.html) comparison explains every distinction.

🔗 Can a foreign key reference something other than a primary key?

Yes. A foreign key can reference either a primary key or any column that carries a UNIQUE constraint in the parent table. The referenced column must hold unique values so each child row matches exactly one parent row.

🔄 What does ON DELETE CASCADE do to child rows?

ON DELETE CASCADE automatically deletes the matching child rows whenever their parent row is deleted, keeping the tables consistent. Alternatives are SET NULL, which clears the child foreign key, and NO ACTION, which blocks the delete.

♻️ Can a foreign key reference a column in the same table?

Yes. A self-referencing foreign key points to a primary key in the same table, which models hierarchies such as an employee row referencing its manager. For self-references, SQL Server recommends ON DELETE NO ACTION to avoid cascade cycles.

🈳 Can a foreign key column contain NULL values?

Yes, unless the column is declared NOT NULL. A NULL foreign key means the child row is not yet linked to any parent row, and SQL Server skips the referential check for that NULL value.

🗑️ What statement removes an existing foreign key constraint?

Run ALTER TABLE child\_table DROP CONSTRAINT fkey\_name. You must supply the constraint name, which you can find in sys.foreign\_keys. Dropping the foreign key removes the relationship but leaves both tables and their data unchanged.

🤖 Can GitHub Copilot generate foreign key constraints?

Yes. [GitHub Copilot](https://github.com/features/copilot) can write FOREIGN KEY constraints inside CREATE TABLE or ALTER TABLE statements from a natural-language prompt and suggest the parent table and referenced column. Always review the keys, referential actions, and data types before running the script.

🧠 How does AI help design table relationships and foreign keys?

AI and machine learning tools examine sample data and query patterns to suggest which columns should become foreign keys, detect missing or orphaned relationships, and recommend suitable ON DELETE actions. The developer reviews each suggestion before applying it.

#### Summarize this post with:

ChatGPT Perplexity Grok Google AI 

**Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** 

##### Sign up for the newsletter

Subscribe for Free 

You have successfully subscribed.  
Please check your inbox. 

![AI-Newsletter]() Chosen by over **350,000+** professionals 

[Scroll to top ](#wrapper)Scroll to top 

× 

Toggle Menu Close 

Search for: 

Search

```json
{"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/sql-server-foreign-key-1.png","url":"https://www.guru99.com/images/sql-server-foreign-key-1.png","width":"700","height":"250","caption":"SQL Server FOREIGN KEY","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/sql-server-foreign-key.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql-server","name":"SQL Server"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/sql-server-foreign-key.html","name":"SQL Server FOREIGN KEY: How to Create with Example"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/sql-server-foreign-key.html#webpage","url":"https://www.guru99.com/sql-server-foreign-key.html","name":"SQL Server FOREIGN KEY: How to Create with Example","dateModified":"2026-07-25T11:19:41+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/sql-server-foreign-key-1.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/sql-server-foreign-key.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown","description":"I'm Fiona brown, a Full Stack Developer with over a decade of experience, sharing practical guides on robust and scalable application development.","url":"https://www.guru99.com/author/fiona","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/fiona-brown-author.png","url":"https://www.guru99.com/images/fiona-brown-author.png","caption":"Fiona Brown","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQL Server","headline":"SQL Server FOREIGN KEY: How to Create with Example","description":"This tutorial covers the definition of a foreign key, rules, how the foreign key works, How to create a foreign key with SQL server management studio, and more.","keywords":"sqlserver, sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"dateModified":"2026-07-25T11:19:41+05:30","image":{"@id":"https://www.guru99.com/images/sql-server-foreign-key-1.png"},"copyrightYear":"2026","name":"SQL Server FOREIGN KEY: How to Create with Example","subjectOf":[{"@type":"HowTo","name":"How to Create FOREIGN KEY in SQL","description":"We can Create a Foreign Key in SQL server in 2 ways:","step":[{"@type":"HowToStep","name":"Step 1) Create new Table","text":"In the first step, Right Click on Tables>New> Table\u2026","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/1/030819_0857_SQLServerFO3.png"},"url":"https://www.guru99.com/sql-server-foreign-key.html#step1"},{"@type":"HowToStep","name":"Step 2) Enter two column name","text":"Now Right click on the Course_Id Column. Now click on Relationship.","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/1/030819_0857_SQLServerFO4.png"},"url":"https://www.guru99.com/sql-server-foreign-key.html#step2"},{"@type":"HowToStep","name":"Step 3) Click on Add","text":"In Foreign Key Relationship, Click Add","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/1/030819_0857_SQLServerFO5.png"},"url":"https://www.guru99.com/sql-server-foreign-key.html#step3"},{"@type":"HowToStep","name":"Step 4) Click on icon show in image","text":"Next, In Table and Column Spec click on icon show in image","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/1/030819_0857_SQLServerFO6.png"},"url":"https://www.guru99.com/sql-server-foreign-key.html#step4"},{"@type":"HowToStep","name":"Step 5) Select Primary Key Table","text":"Now, Select 'Primary Key Table' as 'COURSE' and the new table now being created as 'Foreign Key Table' from the drop down.","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/1/030819_0857_SQLServerFO7.png"},"url":"https://www.guru99.com/sql-server-foreign-key.html#step5"},{"@type":"HowToStep","name":"Step 6) Select 'Course_Id' column","text":"'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.","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/1/030819_0857_SQLServerFO8.png"},"url":"https://www.guru99.com/sql-server-foreign-key.html#step6"},{"@type":"HowToStep","name":"Step 7) Click on Add","text":"Next step, Click on Add.","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/1/030819_0857_SQLServerFO9.png"},"url":"https://www.guru99.com/sql-server-foreign-key.html#step7"},{"@type":"HowToStep","name":"Step 8) Give the Table name","text":"Give the Table name as 'Course_Strength' and click on OK. We have set Parent-child relationship between Course and Course_strength.","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/1/030819_0857_SQLServerFO10.png"},"url":"https://www.guru99.com/sql-server-foreign-key.html#step8"}]},{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between a primary key and a foreign key?","acceptedAnswer":{"@type":"Answer","text":"A primary key uniquely identifies each row inside its own table and cannot be NULL. A foreign key references that primary key from another table to enforce referential integrity. This primary key versus foreign key comparison explains every distinction."}},{"@type":"Question","name":"Can a foreign key reference something other than a primary key?","acceptedAnswer":{"@type":"Answer","text":"Yes. A foreign key can reference either a primary key or any column that carries a UNIQUE constraint in the parent table. The referenced column must hold unique values so each child row matches exactly one parent row."}},{"@type":"Question","name":"What does ON DELETE CASCADE do to child rows?","acceptedAnswer":{"@type":"Answer","text":"ON DELETE CASCADE automatically deletes the matching child rows whenever their parent row is deleted, keeping the tables consistent. Alternatives are SET NULL, which clears the child foreign key, and NO ACTION, which blocks the delete."}},{"@type":"Question","name":"Can a foreign key reference a column in the same table?","acceptedAnswer":{"@type":"Answer","text":"Yes. A self-referencing foreign key points to a primary key in the same table, which models hierarchies such as an employee row referencing its manager. For self-references, SQL Server recommends ON DELETE NO ACTION to avoid cascade cycles."}},{"@type":"Question","name":"Can a foreign key column contain NULL values?","acceptedAnswer":{"@type":"Answer","text":"Yes, unless the column is declared NOT NULL. A NULL foreign key means the child row is not yet linked to any parent row, and SQL Server skips the referential check for that NULL value."}},{"@type":"Question","name":"What statement removes an existing foreign key constraint?","acceptedAnswer":{"@type":"Answer","text":"Run ALTER TABLE child_table DROP CONSTRAINT fkey_name. You must supply the constraint name, which you can find in sys.foreign_keys. Dropping the foreign key removes the relationship but leaves both tables and their data unchanged."}},{"@type":"Question","name":"Can GitHub Copilot generate foreign key constraints?","acceptedAnswer":{"@type":"Answer","text":"Yes. GitHub Copilot can write FOREIGN KEY constraints inside CREATE TABLE or ALTER TABLE statements from a natural-language prompt and suggest the parent table and referenced column. Always review the keys, referential actions, and data types before running the script."}},{"@type":"Question","name":"How does AI help design table relationships and foreign keys?","acceptedAnswer":{"@type":"Answer","text":"AI and machine learning tools examine sample data and query patterns to suggest which columns should become foreign keys, detect missing or orphaned relationships, and recommend suitable ON DELETE actions. The developer reviews each suggestion before applying it."}}]}],"@id":"https://www.guru99.com/sql-server-foreign-key.html#schema-29174","isPartOf":{"@id":"https://www.guru99.com/sql-server-foreign-key.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/sql-server-foreign-key.html#webpage"}}]}
```