Introduction of Key's in sql

Super key

A super key is a column or set of columns that uniquely identifies a row within a table.

Example

Given table: EMPLOYEES{employee_id, firstname, surname, sal}
Possible superkeys are:

• {employee_id}
• {employee_id, firstname}
• ...
• (employee_id, firstname, surname, sal}

Only the the minimal superkey - {employee_id} - will be considered as a candidate key

Candidate key

A candidate key is a key that uniquely identifies rows  in a table . Any of the identified candidate keys can be used as the table's primary key. Candidate keys that are not part of the primary key are called alternate keys.
One can describe a candidate key as a super key that contains only the minimum number of column  necessary to determine uniqueness.

Alternate key

An alternate key is any candidate key that is not the primary key Alternate keys are sometimes referred to as secondary keys.

Primary  key

A primary key is a colunm  in a table  whose values uniquely identify the rows  in the table. The primary key is chosen from this list of candidate  based on its perceived value to the business as an identifier.
A primary key value:

• Must uniquely identiy the row;
• cannot have NULL values;
• Should not change over the time; and
• Should be as short as possible.

 Example

CREATE TABLE t1 (
        c1 int,
        c2 VARCHAR(30),
        c3 VARCHAR(30),
        CONSTRAINT t1_pk PRIMARY KEY (c1,c2));

Composite key

A composite key is a primary key  that consists of more than one column. Composite keys are also known as concatenated or aggregate keys.

Unique key

A unique key is used to uniquely identify rows in an table.

Foreign key

A foreign key is acolumn in a table  that does NOT uniquely identify rows in that table, but is used as a link to matching columns in other tables to indicate a relationship.

For example, the emp.depto column is a foreign key pointing the the dept table's primary key - dept.deptno.