Unique Key

Source: Wikipedia: Unique Key


Unique key

From Wikipedia, the free encyclopedia
Jump to:navigation, search
Merge-arrows.svg
It has been suggested that this article or section be merged with superkey. (Discuss)
Merge-arrows.svg
It has been suggested that this article or section be merged with Key field. (Discuss)

In relational database design, a unique key can uniquely identify each row in a table, and is closely related to the Superkey concept. A unique key comprises a single column or a set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns if NULL values are not used. Depending on its design, a table may have arbitrarily many unique keys but at most one primary key.

Unique keys do not enforce the NOT NULL constraint in practice. Because NULL is not an actual value (it represents the lack of a value), when two rows are compared, and both rows have NULL in a column, the column values are not considered to be equal. Thus, in order for a unique key to uniquely identify each row in a table, NULL values must not be used.

A unique key must uniquely identify all possible rows that exist in a table and not only the currently existing rows. Examples of unique keys are Social Security numbers (associated with a specific person[1][2]) or ISBNs (associated with a specific book). Telephone books and dictionaries cannot use names, words, or Dewey Decimal system numbers as candidate keys because they do not uniquely identify telephone numbers or words.

A primary key is a special case of unique keys. 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. Another difference is that primary keys must be defined using another syntax.

The relational model, as expressed through relational calculus and relational algebra, does not distinguish between primary keys and other kinds of keys. Primary keys were added to the SQL standard mainly as a convenience to the application programmer.

Unique keys as well as primary keys can be referenced by foreign keys.
Contents
[hide]

* 1 Defining primary keys
* 2 Defining unique keys
* 3 Surrogate keys
* 4 Notes
* 5 External links
* 6 See also

[edit] Defining primary keys

Primary keys are defined in the ANSI SQL Standard, through the PRIMARY KEY constraint. The syntax to add such a constraint to an existing table is defined in SQL:2003 like this:

ALTER TABLE <table identifier>
ADD [ CONSTRAINT <constraint identifier> ]
PRIMARY KEY ( <column expression> {, <column expression>}… )

The primary key can also be specified directly during table creation. In the SQL Standard, primary keys may consist of one or multiple columns. Each column participating in the primary key is implicitly defined as NOT NULL. Note that some DBMS require that primary key columns are explicitly marked as being NOT NULL.

CREATE TABLE table_name (
id_col INT,
col2 CHARACTER VARYING(20),

CONSTRAINT tab_pk PRIMARY KEY(id_col),

)

If the primary key consists only of a single column, the column can be marked as such using the following syntax:

CREATE TABLE table_name (
id_col INT PRIMARY KEY,
col2 CHARACTER VARYING(20),

)

[edit] Defining unique keys

The definition of unique keys is syntactically very similar to primary keys.

ALTER TABLE <table identifier>
ADD [ CONSTRAINT <constraint identifier> ]
UNIQUE ( <column expression> {, <column expression>}… )

Likewise, unique keys can be defined as part of the CREATE TABLE SQL statement.

CREATE TABLE table_name (
id_col INT,
col2 CHARACTER VARYING(20),
key_col SMALLINT,

CONSTRAINT key_unique UNIQUE(key_col),

)

CREATE TABLE table_name (
id_col INT PRIMARY KEY,
col2 CHARACTER VARYING(20),

key_col SMALLINT UNIQUE,

)

[edit] Surrogate keys
Main article: Surrogate key

In some design situations the natural key that uniquely identifies a tuple in a relation is difficult to use for software development. For example, it may involve multiple columns or large text fields. A surrogate key can be used as the primary key. In other situations there may be more than one candidate key for a relation, and no candidate key is obviously preferred. A surrogate key may be used as the primary key to avoid giving one candidate key artificial primacy over the others.

Since primary keys exist primarily as a convenience to the programmer, surrogate primary keys are often used—in many cases exclusively—in database application design.

Due to the popularity of surrogate primary keys, many developers and in some cases even theoreticians have come to regard surrogate primary keys as an inalienable part of the relational data model. This is largely due to a migration of principles from the Object-Oriented Programming model to the relational model, creating the hybrid object-relational model. In the ORM, these additional restrictions are placed on primary keys:

* Primary keys should be immutable, that is, not change until the record is destroyed.
* Primary keys should be anonymous integer or numeric identifiers.

However, neither of these restrictions is part of the relational model or any SQL standard. Due diligence should be applied when deciding on the immutability of primary key values during database and application design. Some database systems even imply that values in primary key columns cannot be changed using the UPDATE SQL statement[citation needed].
[edit] Notes

1. ^ SSN uniqueness: Rare SSN duplicates do exist in the field, a condition that led to problems with early commercial computer systems that relied on SSN uniqueness. Practitioners are taught that well-known duplications in SSN assignments[citation needed] occurred in the early days of the SSN system. This situation points out the complexity of designing systems that assume unique keys in real-world data.
2. ^ http://news.yahoo.com/s/ap/us_identity_sharing The Federated States of Micronesia, the Republic of the Marshall Islands and the Republic of Palau still use local social security numbers, which overlap with those of residents of New Hampshire and Maine.

[edit] External links

* Relation Database terms of reference, Keys: An overview of the different types of keys in an RDBMS

[edit] See also

* Globally Unique Identifier
* Natural key

[hide]
v • d • e
Database management systems
Database models · Database normalization · Database storage · Distributed DBMS · Federated database system · Referential integrity · Relational algebra · Relational calculus · Relational database · Relational DBMS · Relational model · Object-relational database · Transaction processing
Concepts
Database · ACID · CRUD · Null · Candidate key · Foreign key · Primary key · Superkey · Surrogate key · Armstrong's axioms
Objects
Relation (Table) · View · Transaction · Log · Trigger · Index · Stored procedure · Cursor · Partition
Components
Concurrency control · Data dictionary · JDBC · ODBC · Query language · Query optimizer · Query plan
Database products: Object-oriented (comparison) · Relational (comparison) · Document-oriented
Retrieved from "http://en.wikipedia.org/wiki/Unique_key"
Categories: Database management systems | Data modeling
Hidden categories: All articles with unsourced statements | Articles with unsourced statements from March 2009 | Articles to be merged from August 2009 | All articles to be merged | Articles to be merged from March 2010 | Articles with unsourced statements from January 2009
Personal tools

* New features
* Log in / create account

Namespaces

* Article
* Discussion

Variants

Views

* Read
* Edit
* View history

Actions

Search

Navigation

* Main page
* Contents
* Featured content
* Current events
* Random article

Interaction

* About Wikipedia
* Community portal
* Recent changes
* Contact Wikipedia
* Donate to Wikipedia
* Help

Toolbox

* What links here
* Related changes
* Upload file
* Special pages
* Permanent link
* Cite this page

Print/export

* Create a book
* Download as PDF
* Printable version

Languages

* Česky
* Deutsch
* Español
* Français
* Bahasa Indonesia
* עברית
* Қазақша
* Nederlands
* 日本語
* ‪Norsk (bokmål)‬
* Polski
* Português
* Русский
* Slovenčina
* Српски / Srpski
* Svenska
* 中文

* This page was last modified on 17 April 2010 at 17:11.
* Text is available under the Creative Commons Attribution-ShareAlike License; additional terms may apply. See Terms of Use for details.
Wikipedia® is a registered trademark of the Wikimedia Foundation, Inc., a non-profit organization.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License