Introduction

A primary key is a table constraint that can be applied on the table to create a primary key on fields. Primary keys uniquely identify each record in a table. Each record in the table contains a unique & non-null value. A table can have only one primary key in a table, the primary key can be created with single or multiple fields.

A foreign key is a table constraint that can be applied on a table to create foreign keys for fields. A foreign key is a key that can be used to associate two tables. A foreign key can be a combination of single or multiple fields in a table that refers to primary keys in another table. A table containing the foreign key is referred to as a child table & the table which holds the candidate key is referred to as the parent table.

The syntax for creating – Primary key  in newly created table

Create table <table name>(
Field <type> Primary key
.
.
Fields
)

The syntax for creating – Primary key  in existing table

ALTER TABLE <Table-Name> ADD PRIMARY KEY (Field);

The syntax for creating – Foreign Key in the newly created table

Create Table <Table name>(
Fields,
.
.
Field <type> References <Table name of primary key holder>(<Primary key field>)
)

The syntax for creating – Foreign Key in existing table

ALTER TABLE <Table-Name>
ADD CONSTRAINT <Constraint-Name> FOREIGN KEY (<Field-Name>) REFERENCES <Parent-Table>
(<Field- Name>)

To understand primary & foreign key concept in depth with the following example :

Creation of primary key in the new table :

“Student” table :

CREATE TABLE Student (
Id int Not Null,
Name varchar(255) Not Null,
Class varchar(255),
Age int,
Primary Key (Id)
)

Output :

In the above example,

“Id” column in the “Student” table is the primary key in the “Student” table.

Creation of primary key in the existing table:

Before creating a foreign key, we will create a table with the name “Employee” as follows :

Create Table Employee(
Id int Not Null,
Name varchar(255) Not Null,
Class varchar(255),
Age int )

Here we have created the “Employee” table, now we are going to add a primary key to the table “Employee” by altering the table .

ALTER TABLE Employee ADD PRIMARY KEY (Id);

After running above command, we have successfully created the Primary key field “Id” in “Employee” table

“Course Schedule” table:

Creation of foreign key in the new table:

CREATE TABLE "Course Schedule"(
Sid int Not Null Primary Key,
WeekDay varchar(255),
scheduleTime varchar(255),
id int References student(id)
)

In the above example,
“id” column in the “Course Schedule” table is a foreign key in the “Course Schedule” table.

Creation of foreign key in the existing table:

Before adding the foreign key constraint, we will create a table with named “Salary ” with the following commands:

Create Table Salary(
id int Not null,
Amount varchar(255) 
)

Here we have created a “Salary” table, now we are creating a foreign key “id” which will refer the primary key “Id” of parent table called “Employee”. We are declaring the “Employee_Salary_Link” constraint to create a foreign key (id) in the “Salary” table.

Related Blogs

1. SQL Joins
2. Schema in SQL
3. Decode in SQL
4. CASE Statement in MySQL
5. Normalization in SQL
6. SQL ORDER BY Clause
7. LIKE Operator in SQL
8. SQL Views
9. SQL Concatenate Function
10. What are the Manipulation Functions in SQL
11. Primary Key In SQL
12. SQL DateTime
13. SQL Functions