Key plays an important role in SQL or Oracle database. Generally it is used for defining a unique set of records. It also establishes the connection ( link) between more than one table.

Types of Keys in SQL or Oracle Database

1. Primary Key (PK)
2. Unique Key
3. Composite/compound Key
4. Alternate Key / Secondary key
5. Foreign Key/References

1. Primary Key

Primary keys can allow only non repeated value (unique value) and it can’t accept null value.

A primary key length cannot be exceeded more than 900 bytes.

Example :

MySQL Command- 

Create table student(stu_id int not null, stu_name varchar(255), stu_address varchar(255), primary key(stu_id))

Command for SQL Server, Oracle DB-

Create table student(stu_id int not null primary key, stu_name varchar(255), stu_address varchar(255));

SQL primary key for multiple columns-

Create table student(stu_id int not null, stu_name varchar(255), stu_address varchar(255), constraints pk_stu_id primary key(stu_id,stu_name))

Note :  You should note that in the above example, there is only one PRIMARY KEY (pk_stu_id). However, it is made up of two columns (stu_id and stu_name).

Use of primary key in alter table command-

Alter table student addprimary key(stu_id);

Drop primary key constraints -

alter table student drop primary key;

2.  Unique Key

It is similar to primary key that means it can’t allow repeated value, but it accept null value.

Example :

SQL Server / Oracle / MS Access Example-

Create table student(stu_id int not null unique, stu_name varchar(255), stu_address varchar(255));

MySQL Example-

Create table student(stu_id int not null, stu_name varchar(255), stu_address varchar(255), unique(stu_id));

3.   Composite/compound Key

When multiple numbers of columns are used as a primary key, it is also known as composite key or composite primary key.

 Example –

Create table test(col1 int, col2 varchar(30), col3 varchar(30), col4 varchar(30),  col5 varchar(30),  primary key (col1,col2,col5));

Note- In the above example, a combination of all three (col1, col2, col5) does uniquely identify each record.

4.  Alternate Key

In simple words, a column which is not a part of the primary key is called alternate keys. It is also known as secondary key.

For Example -

Create table student(stu_id number(5) primary key, stu_name varchar(255), stu_address varchar(255));

Note-  In above example stu_id is primary key and the rest of all columns like stu_name and stu_address are alternate keys.

5.  Foreign key/References

Foreign key is a concept by which we can implement the physical relationship between parent and child table so that the child table can allow thoseentries which is present in the parent table.

In other word you can also say this, the foreign key is used for establishing the link/connection between two tables.

Example-

Parent table student

Create table student(stu_id int not null primary key, stu_name varchar(255), stu_address varchar(255));

Child table fees

Create table fees(fees_id int not null primary key, stu_id intreferences student(stu_id), fees_total int, fees_date int));