Primary key and Foreign key difference with examples

In this tutorial we will learn about primary key ,example of the primary key, syntax of the primary key,what is foreign key,example of the foreign key,syntax of the foreign key,alter table primary key,alter table foreign key and difference between primary key and foreign key.

 

What is Primary key

Primary key is the database constraint must have unique value. Primary key should be not null value. In the table only one column will be the primary key.

Primary key  Example

Suppose we talk about employee table, employee no should be the primary key because employee no is the unique no.name may not primary key as name may be duplicate. department may be duplicate value.

Syntax of the primary key

Using the above example ,we can create a table with primary key using the below syntax.

create table emp ( empno INT NOT NULL AUTO_INCREMENT, empname VARCHAR(30) NOT NULL,empdept VARCHAR(30) NOT NULL, PRIMARY KEY(empno));

Alter table add primary key

if we did not add primary key during table creation we can add primary key using the alter table command.

alter table emp 

add PRIMARY KEY (empno);

What is Foreign key

Foreign key is the database constraint which is used to link two or more tables for a relational database.Foreign key in the one table is the primary key of the other table. It should be the duplicate data and it can be null value.

Foreign key  Example

Suppose we have two tables ane is employee table and other is the department table .employee table contains employee no ,employee name  and employee department and department table contains department id and department name. Department name is the common for both table. In the department table department no is the primary key and in the employee table employee no is the primary key. In the employee table department name is the foreign key and it should be the primary key of the department table.

Syntax of the Foreign key

Using the above example we can create two tables with the primary key for the two tables and foreign key for the employee table using the below syntax.

create table dept( deptno INT NOT NULL AUTO_INCREMENT,deptname VARCHAR(30) NOT NULL, PRIMARY KEY(deptno));

create table emp ( empno INT NOT NULL AUTO_INCREMENT, empname VARCHAR(30) NOT NULL,empdept VARCHAR(30) NOT NULL, PRIMARY KEY(empno),FOREIGN KEY(empdept) REFERENCES dept(deptname));

Alter table add primary key

if we did not add Foreign key during table creation we can add foreign key using the alter table command.

alter table emp 

ADD FOREIGN KEY(empdept) REFERENCES dept(deptname);

Primary key and Foreign key

Difference between primary key and foreign key

Primary key                                                                Foreign key

1.Primary key should be unique.                             1.Foreign key should not be unique. It can be                                                                                                  duplicate.    

2.value should not be NULL.                                  2.Foreign key may be null

3.only one column should be primary key              3.many columns may be foreign key.

In this tutorial we have learned about primary key ,example of the primary key, syntax of the primary key,what is foreign key,example of the foreign key,syntax of the foreign key,alter table primary key,alter table foreign key and difference between primary key and foreign key.

SHARE

Admin

• 
• 
• 
• 
•