• Phil Factor (7/17/2014)


    @Sergei

    Please correct me if I'm wrong but I'd always believed a primary key was enforced by a constraint which was implemented by means of both a UNIQUE index and NOT NULL constraints on all the participating columns.

    @Phil, you are wrong (but your QotD is correct, of course, a primary key is not an index).

    A primary key is not a constraint. There are two acceptable definitions in relational theory: (1) it is the candidate key (set of columns) chosen to be the principal means of reaching/identifying individual rows in a relation, or (2) it is the value of a primary key as defined by (1) in a particular row.

    Neither of those things is a primary key constraint. A primary key constraint is a constraint that ensures that the primary key is fully defined (ie includes no NULL values) in every row, and that the projection of the relation onto the columns involved in the primary key contains no duplicates, ie the set of columns involved in the primary key is a superkey so it can be used to reach or identify an individual row - while theory talks about candidate keys and database design attempts to discover them by understanding what real world rules apply to the data, an RDBMS can't implement a constraint which enforces the candidate key property other than for single column keys - in effect the constraint that the primary key be a candidate key rather than just a superkey is a theoretical (and often ignored or broken) constraint on the schema design, not a constraint on the data.

    The primary key constraint is implemented as a set of constraints: a domain constraint for each column involved in the key that specifies that the domain excludes NULL and a uniqueness constraint that ensures there are no duplicates; the uniqueness constraint is in turn implemented (in almost every RDBMS) using an index.

    Tom