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