--- 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 FOREIGN KEY: How to Create in SQL Server with Example image: https://www.guru99.com/images/sql-server-foreign-key.png --- [Skip to content](#main) ## 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 [![Rules for FOREIGN KEY in SQL](https://www.guru99.com/images/1/030819_0857_SQLServerFO1.png)](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO1.png) How Foreign Key Works ## 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. [](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’ as two columns. However, ‘Course\_ID’ shall be 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 name as ‘Course\_ID’ and ‘Course\_Strength.’ Right click on ‘Course\_Id’ Column. Now click on Relationship. [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO4.png) **Step 3)** In **‘Foreign Key Relationship**,’ Click **‘Add’** [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO5.png) **Step 4)** In ‘Table and Column Spec’ click on **‘…’ icon** [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO6.png) **Step 5)** Select ‘Primary Key Table’ as ‘COURSE’ and the new table now being created as ‘Foreign Key Table’ from the drop down. [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO7.png) **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. [](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 on **OK.** [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO10.png) **Result:** We have set Parent-child relationship between **‘Course’** and **‘Course\_strength.’** [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO11.png) ### 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. [](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 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:** ### RELATED ARTICLES * [SSIS Tutorial for Beginners: What is, Architecture, Packages ](https://www.guru99.com/ssis-tutorial.html "SSIS Tutorial for Beginners: What is, Architecture, Packages") * [SQL Server Database: Create, Alter, & Drop Database in SQL ](https://www.guru99.com/sql-server-database-create-alter-drop-restore.html "SQL Server Database: Create, Alter, & Drop Database in SQL") * [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)") * [Top 20 SSRS Interview Questions and Answers (2026) ](https://www.guru99.com/ssrs-interview-questions.html "Top 20 SSRS Interview Questions and Answers (2026)") 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. [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO13.png) **Result:** We have set 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 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; [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO15.png) 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'); [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO16.png) **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. [](https://www.guru99.com/images/1/030819%5F0857%5FSQLServerFO17.png) ## Summary * Every value of Foreign key has to be part of [Primary Key](https://www.guru99.com/sql-server-primary-key.html) 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](https://www.guru99.com/sql-server-management-studio.html). * Here is the difference between Primary Key vs Foreign Key: [Click Here](https://www.guru99.com/difference-between-primary-key-and-foreign-key.html) #### 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.png","url":"https://www.guru99.com/images/sql-server-foreign-key.png","width":"328","height":"285","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 FOREIGN KEY: How to Create in SQL Server 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 FOREIGN KEY: How to Create in SQL Server with Example","dateModified":"2024-06-28T15:48:16+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/sql-server-foreign-key.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"}},{"image":{"@id":"https://www.guru99.com/images/sql-server-foreign-key.png"},"headline":"SQL FOREIGN KEY: How to Create in SQL Server 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","@type":"Article","author":{"@id":"https://www.guru99.com/author/fiona","name":"Fiona Brown"},"dateModified":"2024-06-28T15:48:16+05:30","copyrightYear":"2024","name":"SQL FOREIGN KEY: How to Create in SQL Server with Example","articleSection":"SQL Server","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"}]}],"@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"}}]} ```