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.

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.

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. 

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.

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.