<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Database Concepts: Keys

Published on
14,313 Points
8,013 Views
3 Endorsements
Last Modified:
Approved
Community Pick
In this part, I will be briefing about different kind of keys available in database systems.
Base example I will be using the following table to explain about database keys -

EMPLOYEE [] EMPLOYEE_ID, EMPLOYEE_SSN_ID, EMPLOYEE_DEPT_ID, EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME, EMPLOYEE_ADDRESS ]

Candidate Key

A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. In general, one of these candidate keys is selected as the table primary key.

Example - From the above table EMPLOYEE_ID, EMPLOYEE_SSN_ID, and EMPLOYEE_DEPT_ID can be considered as candidate keys

Primary Key

A primary key is a single column or combination of columns that uniquely defines a record. None of the columns that are part of the primary key can contain a null value. A table can have only one primary key.

Example - EMPLOYEE_ID or EMPLOYEE_SSN_ID can be considered as primary keys

Unique Key

A unique key or primary key [is a candidate key] to uniquely identify each row in a table. It be comprised of either a single column or multiple columns.

The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is enforced. Thus, the values in unique key columns may or may not be NULL.

Differences between Primary Key and Unique Key

Primary Keys -
1. It will not accept null values.       
2. There will be only one primary key in a table.       
3. Clustered index is created in Primary key.       
4. Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist.       

Unique Keys -
1. Null values are accepted.
2. More than one unique key will be there in a table.
3. Non-Clustered index is created in unique key.
4. Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Alternate Key

A candidate key that is not the primary key is called an alternate key.

Example - If EMPLOYEE_ID is considered as primary keys then EMPLOYEE_SSN_ID is an alternate key.

Superkey

A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

A primary key is therefore a minimum superkey.

Examples - Any combination of the following can be considered as a Super key

- EMPLOYEE_ID - Minimal Super Key

- EMPLOYEE_ID and EMPLOYEE_SSN_ID

- EMPLOYEE_ID, EMPLOYEE_SSN_ID and EMPLOYEE_DEPT_ID

- EMPLOYEE_ID, EMPLOYEE_SSN_ID, EMPLOYEE_DEPT_ID, EMPLOYEE_FIRST_NAME

- EMPLOYEE_SSN_ID, EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME

Foreign Key

The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.

Composite Key

A primary key that made up of more than one attribute is known as a composite key.

Example - [] EMPLOYEE_ID and EMPLOYEE_SSN_ID ] can together be treated as (one of) composite keys. Another combination can be [] EMPLOYEE_ID, EMPLOYEE_SSN_ID and EMPLOYEE_DEPT_ID ]

Surrogate Key

Surrogate keys are keys that have no business meaning 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).

Further Reading

Please visit my blog

References

http://en.wikipedia.org/wiki/Candidate_key

http://databases.about.com/od/specificproducts/a/keys.htm
3
Comment
Author:Jagadeesh M
2 Comments
LVL 22

Expert Comment

by:dportas
"Clustered index is created in Primary key"
This is not true. Indexes have nothing to do with keys. I don't think there is any DBMS that requires clustered indexes to be created on the primary key. Usually such indexes can be created on any set of columns.
0
LVL 55

Expert Comment

by:Mark Wills
Well,

There are a few databases out there that will automatically make the primary key a clustered index if one (ie clustered index) doesnt already exist. So, it might depend on which way the Author was thinking at the time...

But it is also true that Primary Keys do not have to be clustered indexes, and I guess that is dportas' real point.

By the same token, a clustered index is typically considered unique, but if you dont specify unique then some databases will accept that and automatically add in a uniquifier...

Interesting how some databases have implemented the theories of database (more specifically relational) design.
0

Featured Post

OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Join & Write a Comment

Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month