A Candidate Key (CK) is a column or group of columns that uniquely describe every row in a table.
For instance, in a table that describes people (employees, customers, club members, whatever), there are many columns that describe those people. If you accept the assertion that you can't have two people with the same name (which is a VERY bad idea in my experience), one of your CKs could be the person's name. If you accept the assertion that you can't have two people with a given phone number (again, a VERY bad idea in my experience), one of your CKs could be the phone number. Especially with people, just about every CK I can think of is subject to exceptions.
A Surrogate Key (SK) is an arbitrary value that is used solely by the database (and therefore by any programs that use that database) to uniquely identify a row. This can be a GUID, an "automagically" assigned number, or just some arbitrary value that happens to be unique for every row. SKs are guaranteed to be unique (that is part of their definition), so they are always CKs. Using an SK can be extremely convenient because it allows the database to function independently of any changes to the business rules that make other CKs unique.
For More Information
- Dozens more answers to tough database design questions from Pat Phelan
- The Best Database Design Web Links: tips, tutorials, scripts, and more
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in September 2002