Don't let the Lockdown slow you Down - Enroll Now and Get 3 Course at 24,999/- Only. Explore Now!

General

Primary and Foreign Key in SQL

Primary and Foreign Key in SQL

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.

Primary and Foreign Key

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 :

idnameclassAge
1samirFYBA25
2ankitFYSC25
3kashishSYBA27
4rekhaTYBA28
CREATE TABLE Student (
Id int Not Null,
Name varchar(255) Not Null,
Class varchar(255),
Age int,
Primary Key (Id)
)

Output :

Primary Key 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 )

Primary Key Existing Table

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);

Alter Employee Primary id

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

“Course Schedule” table:

sididweekdayTime
X0012Mon to Wed8 AM to 9 AM
X0021Wed to Sat12 PM to 1 PM
X0034Fri to Dat3 PM to 4 PM
X0043Mon to Sun5 PM to 6 PM

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)
)

Create Foreign KeyIn 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) 
)

Create Foreign Key Existing Table
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.

Employee 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

 

Besant Technologies WhatsApp