Keys

It is important to be able to specify how rows in relation are distinguished conceptually, rows are distinct from one another, but from a database perspective the difference among them must be expressed in terms of their attributes. Keys come here for a rescue!

Primary key

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify all table records.

A primary key’s main features are:

• It must contain a unique value for each row of data.
• It cannot contain null values.
• A primary key is either an existing table column or a column that is specifically generated by the database according to a defined sequence.

Candidate key

A candidate key is the most minimal subset of fields that uniquely identifies a tuple. Candidate keys are defined as the set of fields from which primary key can be selected. It is an attribute or set of attribute that can act as a primary key for a table to uniquely identify each record in that table.

 Foreign Key

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table. In simpler words, the foreign key is defined in a second table, but it refers to the primary key in the first table. A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. It acts as a cross-reference between tables because it references the primary key of another table, thereby establishing a link between them.

Super key

A super key is defined in the relational model of database organization as a set of attributes of a relation variable for which it holds that in all relations assigned to that variable, there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a super key can also be defined as a set of attributes of a relation schema upon which all attributes of the schema are functionally dependent. A super key is a set of fields that contains a key.

Unique Key

Unique Key can be a field or set of fields that can be used to uniquely identify the tuple from the database. One or more fields can be declared as a unique Key. The unique Key column can also hold the NULL value. Use of Unique Key improves the performance of data retrieval. It makes searching for records from the database much more faster & efficient.

Alternate Key

Alternate Key is that Key which can be used as a Primary Key if required. Alternate Key also qualifies to be a Primary Key but for the time being, It is not the Primary Key.

Composite Key

Composite Key is also known as Compound Key / Concatenated Key. Composite Key refers to a group of two or more columns that can be used to identify a tuple from the table uniquely. A group of the column in combination with each other can identify a row uniquely but a single column of that group doesn’t promise to identify the row uniquely.