SQL: Keys

Keys are, as their name suggests, a key part of a relational database and a vital part of the structure of a table. They ensure each record within a table can be uniquely identified by one or a combination of fields within the table. They help enforce integrity and help identify the relationship between tables. There are three main types of keys, candidate keys, primary keys and foreign keys. There is also an alternative key or secondary key that can be used, as the name suggests, as a secondary or alternative key to the primary key.

These are the main types of Keys:

Super Key:  An attribute or a combination of attribute that is used to identify the records uniquely. A table can have many Super Keys. Eg.:

ID

ID, Name

Name, Address, Department_ID

Candidate Key: It can be defined as minimal Super Key or irreducible Super Key. In other words an attribute or a combination of attribute that identifies the record uniquely but none of its proper subsets can identify the records uniquely. Eg.: Name, Address

Combination of Name and Address can identify the record uniquely, but neither Name nor Address can be used to identify the records uniquely as it might be possible that we have two records with similar name or two records with the same address.

Primary Key: A Candidate Key that is used by the database designer for unique identification of each row in a table is known as Primary Key. A Primary Key can consist of one or more attributes of a table. eg.: ID

Foreign Key:  A foreign key is an attribute or combination of attribute in one base table that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. eg.:

Department_ID

Department_Name

Manager_ID

Location_ID

A Foreign Key value must match an existing value in the parent table or be NULL.

Composite Key: If we use multiple attributes to create a Primary Key then that Primary Key is called Composite Key (also called a Compound Key or Concatenated Key). eg.: Name, Address

Alternate Key: Alternate Key can be any of the Candidate Keys except for the Primary Key. eg.: Name, Address

Secondary Key: The attributes that are not even the Super Key but can be still used for identification of records (not unique) are known as Secondary Key. eg.:

Name

Address

Salary

Department_ID

These can identify the records but they might not be unique.

Surrogate key: A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural (or business) key which is derived from application data. Surrogate keys are keys that have no businessmeaning and are solely used to identify a record in the table.

Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Unique Key: Unique key constraints are used to ensure that data is not duplicated in two rows in the database. One row in the database is allowed to have null for the value of the unique key constraint. Although a table should have a PK, it need not have any additional unique keys. However, tables can have more than one unique key if that meets your needs. Like PKs, unique keys can span multiple columns. eg. Order ID. Value can be null but if it is not null then it has to be unique.

Difference between Key, Foreign Key, Primary Key and Unique Key:

KEY refers to a normal non-unique index. Non-distinct values for the keys are allowed, so the key may contain rows with identical values in all columns of the index. These keys don't enforce any restraints on your data so they are used only for making sure certain queries can run quickly.

FOREIGN Key (sometimes called a referencing key) is a key used to link two tables together. Typically you take the primary key field from one table and insert it into the other table where it becomes a foreign key (it remains a primary key in the original table).

PRIMARY Key acts exactly like a UNIQUE key except that it is always named 'PRIMARY'. A PRIMARY key is a combination of fields which uniquely specify a row. This is a special kind of unique key, and it has implicit NOT NULL constraint. It means, Primary key values cannot be NULL.

UNIQUE Key refers to an index where all rows of the index must be unique. That is, the same row may not have identical non-NULL values for all columns in this index as another row. As well as being used to speed up queries, UNIQUE keys can be used to enforce restraints on data, because the database system does not allow this distinct values rule to be broken when inserting or updating data.

Your database system may allow a UNIQUE key to be applied to columns which allow NULL values, in which case two rows are allowed to be identical if they both contain a NULL value (the rationale here is that NULL is considered not equal to itself). Depending on your application, however, you may find this undesirable: if you wish to prevent this, you should disallow NULL values in the relevant columns.

There is 1 more key (that I have read about but never worked with):

FULLTEXT Keys are different from all of the above, and their behavior differs significantly between database systems. FULLTEXT indexes are only useful for full text searches done with the MATCH() / AGAINST() clause, unlike the above three - which are typically implemented internally using b-trees (allowing for selecting, sorting or ranges starting from left most column) or hash tables (allowing for selection starting from left most column).

Where the other Key types are general-purpose, a FULLTEXT key is specialized, in that it serves a narrow purpose: it's only used for a "full text search" feature. You can only use FULLTEXT (or otherwise called SEARCH INDEX) with Innodb (In MySQL 5.6.4 and up) and Myisam Engines. FULLTEXT can be used on CHAR, VARCHAR and TEXT column types

Key tit-bits:

The one thing that primary, unique, and foreign keys all have in common is the fact that each type of key can consist of more than just one column from a given table. In other words, foreign, primary, and unique keys are not restricted to having just one column from a given table – each type of key can cover multiple columns. That said, these are major differences

A table can have multiple unique and foreign keys. However, a table can have only one primary key.

Unique key columns are allowed to hold NULL values. The values in a primary key column, however, can never be NULL.

A Foreign key can reference a key that is not the primary key of a table. But, a foreign key must reference a unique key.

A Foreign key can hold NULL values. Because foreign keys can reference unique, non-primary keys – which can hold NULL values – this means that foreign keys can themselves hold NULL values as well.