Primary key

From wiki.gis.com
Jump to: navigation, search

An attribute or set of attributes in a database that uniquely identifies each record in a table. A primary key allows no duplicate values and cannot be null.[1] A primary key is a column (or concatenation of columns) that comprises unique values and is designated by the user or the database system. However not all fields of unique values are primary keys, for example, a table that contains unique values in both a CustomerID field and a Social Security number field may have only the CustomerID field designated as the primary key.

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 as follows:[2]

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

References

  1. Wade, Tasha, ed.; Sommer, Shelly, ed. A to Z GIS: An illustrated dictionary of geographic information systems, ESRI Press, 2006
  2. Wikipedia contributors, Unique key, Wikipedia, The Free Encyclopedia, Accessed 4 June 2010.

See Also