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;
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
Share your thoughts in the comments
Please Login to comment...