SQL Query to Add Foreign Key Constraints Using ALTER Command
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;
Use the below command to use the geeks database:
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.