A Comprehensive Guide to SQL Keys And Its Types

What is the Key ?

• Keys play an important role in the relational database.

• It is used to uniquely identify any record or row of data from the table. It is also used to establish and identify relationships between tables.

Types of keys:

Primary Key

Foreign Key'

Composite Key

Unique Key

Alternate Key

SQL PRIMARY KEY :

• A column or columns is called primary key (PK) that uniquely identifies each row in the table.

• If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.

• When multiple columns are used as a primary key, it is known as composite primary key.

• In designing the composite primary key, you should use as few columns as possible.

• It is good for storage and performance both, the more columns you use for primary key the more storage space you require.

Points to remember for primary key:

• Primary key enforces the entity integrity of the table.

• Primary key always has unique data.

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

• A primary key cannot have null value.

• There can be no duplicate value for a primary key.

• A table can contain only one primary key constraint.

SQL FOREIGN KEY

• In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.

• In simple words you can say that, a foreign key in one table used to point primary key in another table.

• Let us take an example to explain it:

Here are two tables first one is students table and second is orders table.

Here orders are given by students.

First table:

Second table:

Here you see that "S_Id" column in the "Orders" table points to the "S_Id" column in "Students" table.

• The "S_Id" column in the "Students" table is the PRIMARY KEY in the "Students" table.

• The "S_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The foreign key constraint is generally prevents action that destroy links between tables.It also prevents invalid data to enter in foreign key column.

SQL Composite Key

• A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

• Sometimes more than one attributes are needed to uniquely identify an entity.

• A primary key that is made by the combination of more than one attribute is known as a composite key.

• In other words we can say that: Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

For example, in employee relations, we assume that an employee may be assigned multiple roles, and an employee may work on multiple projects simultaneously. So the primary key will be composed of all three attributes, namely Emp_ID, Emp_role, and Proj_ID in combination. So these attributes act as a composite key since the primary key comprises more than one attribute.

Unique Key in SQL

• A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table.

• You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.

• The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.

• There is an automatically defined unique key constraint within a primary key constraint.

Alternate Key in SQL

• Alternate key is a secondary key it can be simple to understand by an example:

• Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS.

  Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

• If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

Super Key in SQl

Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), the name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this combination can also be a key.

Alternate key in SQL

• There may be one or more attributes or a combination of attributes that uniquely identify each tuple in a relation.

• These attributes or combinations of the attributes are called the candidate keys.

• One key is chosen as the primary key from these candidate keys, and the remaining candidate key, if it exists, is termed the alternate key.

• In other words, the total number of the alternate keys is the total number of candidate keys minus the primary key.

• The alternate key may or may not exist. If there is only one candidate key in a relation, it does not have an alternate key.

For example, employee relation has two attributes, Employee_Id and PAN_No, that act as candidate keys. In this relation, Employee_Id is chosen as the primary key, so the other candidate key, PAN_No, acts as the Alternate key.