Unique key

Unique key

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. According to the SQL[1] standard and Relational Model theory, a unique key (unique constraint) should accept NULL in several rows/tuples — however not all RDBMS implement this feature correctly[2][3].

A unique key should 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[4]) 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 table can have at most one primary key, but more than one unique key. A primary key is a combination of columns which uniquely specify a row. It is a special case of unique keys. One difference is that primary keys have an implicit NOT NULL constraint while unique keys do not. Thus, the values in unique key columns may or may not be NULL, and in fact such a column may contain at most one NULL fields.[5] 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

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 explicitly marking primary-key columns as 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),
     ...
  )

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,
     ...
  )

Surrogate keys

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 changed 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].

Alternate key

It is commonplace in SQL databases to declare a single primary key, the most important unique key. However, there could be further unique keys that could serve the same purpose. These should be marked as 'unique' keys. This is done to prevent incorrect data from entering a table (a duplicate entry is not valid in a unique column) and to make the database more complete and useful. These could be called alternate keys. [6]

See also

References

  1. ^ Summary of ANSI/ISO/IEC SQL
  2. ^ Constraints - SQL Database Reference Material - Learn sql, read an sql manual, follow an sql tutorial, or learn how to structure an SQL query
  3. ^ Comparison of different SQL implementations
  4. ^ 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.
  5. ^ MySQL 5.5 Reference Manual :: 12.1.14. CREATE TABLE Syntax "For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL."
  6. ^ Alternate key - Oracle FAQ

External links


Wikimedia Foundation. 2010.

Игры ⚽ Поможем решить контрольную работу

Look at other dictionaries:

  • unique key — individualusis raktas statusas T sritis informatika apibrėžtis ↑Raktas (3), skirtas vienam tinklo naudotojui. atitikmenys: angl. individual key; pairwise key; per station key; unique key ryšiai: dar žiūrėk – raktas palygink – grupinis raktas …   Enciklopedinis kompiuterijos žodynas

  • Unique key — Клавиша специального знака …   Краткий толковый словарь по полиграфии

  • Derived unique key per transaction — In cryptography, Derived Unique Key Per Transaction (DUKPT) is a key management scheme in which for every transaction, a unique key is used which is derived from a fixed key. Therefore, if a derived key is compromised, future and past transaction …   Wikipedia

  • Key — may refer to: Building* Key, Carpentry: timber or metal wedges used across or between two or more members to act as a tightening agent. * Key, Painting: to rough the surface of previous coats of paint to allow a secure bond for the next or top… …   Wikipedia

  • Key Sequenced Data Set — A Key Sequenced Data Set (KSDS) is a type of data set used by the IBM VSAM computer data storage system. Each record in a KSDS data file is embedded with a unique key. A KSDS consists of two parts, the data component and a separate index file… …   Wikipedia

  • Key Largo Woodrat — Conservation status Endangered ( …   Wikipedia

  • Key lime — Tree ripened key lime. Color is bright yellow, unlike the more common green Persian limes. Scientific classification Kingdom: Plantae …   Wikipedia

  • Key selection vector — The Key Selection Vector means the numerical associated with a Device Key Set and distributed by Licensor or its designee to Adopters and used to support authentication of Licensed Products and Revocation. It is considered a confidential set of… …   Wikipedia

  • Key (music) — Scale degree names[1] (C major scale). In music theory, the term key is used in many different and sometimes contradictory ways. A common use is to speak of music as being in a specific key, such as in the key of C major or in the key of F sharp …   Wikipedia

  • Key the Metal Idol — Infobox animanga/Header name = Key the Metal Idol caption = ja name = ja name trans = genre = Drama, MechaInfobox animanga/OVA title = director = Hiroaki Sato studio = Studio Pierrot episodes = 15 released = 16 December 1994 Key the Metal Idol is …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”