- To avoid duplication, we need to define constrains on data.
- For example, all students will have unique roll number.
- Here are primary key, foreign key, unique key etc.
Primary Key
- Primary key is column inside table that can be used to uniquely identify a record.
- For example, we can get record of particular employee based
- on his employee number.
- We can define primary key at the time of table creation.
- Syntax: create table <tableName> (<columnName><datatype>(<size>)Primary key));
- Example : CREATE TABLE Employee (Emp_Id int PRIMARY KEY IDENTITY(1,1), LastName varchar(255) NOT NULL, FirstName varchar(255), LastName varchar(255), Add varchar(255), City varchar(255)
- Here Emp_Id field is primary key & auto incremented
- which has been specified by Identity(1,1)
- There are some attributes of primary key.
- It will not allow duplicate values.
- It will not allow null values.
- Only one primary key is allowed per table.
- It’s main purpose is record uniqueness.
Foreign Key
- When one column is primary key in one table and being referred in second table it is called foreign key in second table.
- For example, Emp_Id of employee will be referred in personal information table, leave table, salary table and so on..
- Syntax: <columnName><datatypa>(<size>) references <tableName> [(<columnName>)]
- Example:
- create table Salary_Master(ID int primary key, Emp_ID numeric(5,0) references Employee_Master, basic numeric(5,0) date datetime);
- This will refer Emp_Id from Employee_Master table and inside Employee_Master it is primary key.
- Attributes of foreign key are…
- Parent that is being referred must have primary key.
- Parent record can be deleted if there is no record in child.
- Child may have duplicate value from parent but parent can not have.
- Record can not be inserted in child if corresponding record does not exist in parent table.
Unique Key
- When column inside the table is defined as unique key, this key can be used to uniquely identify a record.
- It is similar to primary key but difference is it allows null inside column where as primary key will not allow null value.
- A table can have more than one unique key but it is not possible with primary key.
