Primary Key, Foreign Key and Unique Key

    18 Votes

To understand the ordering of data inside a table, one had to be thorough about database keys.This article will provide an insight into Primary Key, Foreign Key and Unique Key.

SQL Primary Key

A primary key is a column whose values uniquely identify every row in a table. To define a field as primary key, following conditions had to be met :
  • No two rows can have the same primary key value.
  • Every row must have a primary key value
  • Primary key field can't be null
  • Values in primary key columns can never be modified or updated.

 

Primary key values can never be reused. If a row is deleted from the table, its primary key may not be assigned to any new rows in the future. In the examples given below, “Employee_ID” field is the primary key.
primary key

SQL Composite Primary Key

A Composite primary key is a  set of columns whose values uniquely identify every row in a table. For example, in the table given above , if "Employee_ID" and "Employee Name" together uniquely identifies a row its called a Composite Primary Key . In this case , both the columns will be represented as primary key.

Read SQL Interview Questions for frequently asked questions on SQL. 

SQL Foreign Key

When, "one" table's primary key field is added to a related "many" table in order to create the common field which relates the two tables, it is called a foreign key in the "many" table. In the example given below, salary of an employee is stored in salary table. Relation is established via foreign key column “Employee_ID_Ref” which refers “Employee_ID” field in Employee table.

For example, salary of "Jhon" is stored in "Salary" table. But his employee info is stored in "Employee" table. To identify the salary of "Jhon", his "employee id" is stored with each salary record. 

Foreign Key

The advantage of using foreign key is that, the data is not getting duplicated. If foreign key concept was not there in RDBMS, entire info of an employee, such as First Name, Last Name, Id etc. had to be stored with each and every salary entry. Another advantage of foreign key is that the editing master entry such as designation, address, etc. wont have any impact on the child table.

Read Advanced SQL Query Interview Questions and Answers on SQL Server, MySQL and Oracle.

SQL Unique Key

Unique key is same as primary with difference being the existence of null. Unique key field allows one value as NULL value. It wont allow duplicate entries.

Concept of Primary Key, Foreign Key and Unique Key is same in all the databases.

Popular Videos

communication

How to improve your Interview, Salary Negotiation, Communication & Presentation Skills.

Got a tip or Question?
Let us know

Related Articles

RDBMS and DBMS Introduction, Concepts and Basics
SQL Create Table, DROP Table Syntax and SQL Default Command
SQL Select with Order By, Insert, Update and Delete Command
SQL Operators - Where, IN, AND, OR and Between
SQL Like and Distinct Command
SQL Join Command - Inner, Outer and Left Join
SQL Mathematical Functions, Concat and Trim Commands
SQL Group By and Having Command
Difference between having and where clause
Difference between Inner join and Left join
Clustered Index vs Non Clustered Index