Open In App

SQL Query to Add Foreign Key Constraints Using ALTER Command

Improve
Improve
Like Article
Like
Save
Share
Report

In this article, we will look into how you can add a foreign key constraint using the ALTER command in SQL. For this article, we will be using Microsoft SQL Server as our database.

But before we start let’s take a look at the below terminologies:

  • Primary key: It is used to uniquely identify the records in the table. It is mainly used in Relational Database. A Primary must be unique.
  • Foreign Key: It is used to link tables together. This is also called referencing. It is used for referring to a Primary Key of another table.

 

Create a Database:

We can create a Database using the command:

Syntax: CREATE DATABASE DATABASE_NAME;

So let’s create a geeks database as shown below:

CREATE DATABASE geeks;

Using Database:

Use the below command to use the geeks database:

use geeks;

Adding table into Database:-

To add a table into the database we use the below command:

Syntax:- CREATE TABLE table_name (Attribute_name datatype...);

So, let’s create a geeks table within the geeks database as shown below:

CREATE TABLE geeks(id int , name varchar(20));

Here Table Added Successfully.

Inserting values into Tables:

For inserting records into the table we can use the below command:

Syntax: INSERT INTO table_name(column1,
                column2,
                column 3,.....)
                 VALUES( value1,
                value2,
                value3,.....);

So let’s add some records in the geeks table:

 INSERT INTO geeks(id,name) VALUES (1,"teja");

Creating Primary Key Element in a Table:

To have a Foreign Key in a Table we must have a Primary Key. To create a Primary we use the below command:

Syntax: CREATE TABLE table_name (Attribute_name  datatype PRIMARY_KEY);

Now let’s create a primary key:

CREATE TABLE emp (id int NOT NULL PRIMARY KEY,name varchar(20))

Now to add a Foreign Key we have to create a new table by the following:

CREATE TABLE  student(
id int ,
name varchar(20),
Emp_id int REFERENCES emp(id));

Alter a Table and ADD Foreign Key:

So if you already created the table student, and now you wish to add Foreign Key you can use the below command to change that:

ALTER TABLE dbo.student 
add  constraint Fk_empid foreign key(emp_id)
references dbo.emp(id);

At this point, we have successfully achieved our goal.


Last Updated : 19 Apr, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads