---
title: "Types of keys in SQL or Oracle Database."  
description: "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 ("  
author: "Arti Mishra"  
published: 2018-09-10  
updated: 2018-09-11  
canonical: https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database  
category: "database"  
tags: ["mysql", "sql"]  
reading_time: 3 minutes  

---

# Types of keys in SQL or Oracle Database.

Key plays an important role in SQL or [Oracle database](https://answers.mindstick.com/qa/104808/how-to-connect-to-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](https://www.mindstick.com/interview/34482/keys-in-sql-server-complete-guide) or Oracle Database**

1. [Primary Key](https://www.mindstick.com/blog/474/remove-primary-key-from-access-table) (PK)
2. Unique Key
3. Composite/compound Key
4. Alternate Key / Secondary key
5. [Foreign Key](https://www.mindstick.com/blog/174/foreign-key-self-reference-constraint)/References

## \

## 1. Primary Key

[Primary keys](https://answers.mindstick.com/qa/30985/what-are-primary-keys-and-foreign-keys) can allow only **[non repeated](https://answers.mindstick.com/qa/35071/how-to-find-first-non-repeated-character-of-a-given-string) value (unique value)** and it **can’t accept null value.**

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

## Example :

**M****ySQL Command-**

[Create table](https://www.mindstick.com/articles/443/how-to-create-table-in-sql-server) 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](https://www.mindstick.com/forum/156805/how-to-set-primary-key-constraints-on-multiple-columns-in-a-table-in-sql-server)-**

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](https://www.mindstick.com/forum/159765/how-do-you-rename-a-table-in-sql-using-the-alter-table-statement) command-**

Alter table student add primary 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](https://www.mindstick.com/forum/161071/what-is-mongodb-and-explain-it-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 those** **entries 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 int references student(stu_id), fees_total int, fees_date int));

\

---

Original Source: https://www.mindstick.com/articles/13115/types-of-keys-in-sql-or-oracle-database

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.