Primary and Foreign Key in SQL
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) )
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:
|X001||2||Mon to Wed||8 AM to 9 AM|
|X002||1||Wed to Sat||12 PM to 1 PM|
|X003||4||Fri to Dat||3 PM to 4 PM|
|X004||3||Mon to Sun||5 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) )
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.
- SQL Joins
- Schema in SQL
- Decode in SQL
- CASE Statement in MySQL
- Normalization in SQL
- SQL ORDER BY Clause
- LIKE Operator in SQL
- SQL Views
- SQL Concatenate Function
- What are the Manipulation Functions in SQL
- Primary Key In SQL
- SQL DateTime
- SQL Functions