Sfrgttk

I know the concept of candidate key in RDBMS theory. But do candidate keys really exist in actual SQL engines? I mean is there anyway to designate particular column or a set of columns as candidate key in any of database systems, say SQL Server, postgres, MySQL, Oracle etc.? Is there any reserved keyword for designating column as candidate key like PRIMARY KEY or UNIQUE in case of primary key column and unique key column? I feel UNIQUE constraint itself provides implementation of the candidate key concept. I dont see any practical value of separate CANDIDATE KEY keyword. Is it so?

As far as I know, no SQL database management system (DBMS) supplies the CANDIDATE KEY keyword as such, but that does not mean that the notion (or the functionality) of candidate key cannot be configured in a SQL table.

How to represent a candidate key

For example, if

• there is no primary declared for the table under consideration, and


• a whole set of columns (i.e., one or more) that is configured with a UNIQUE constraint is also fixed with the corresponding NOT NULL constraint(s),

then the designer is, precisely, representing a candidate key.

For example, the following table shows three distinct candidate keys:

CREATE TABLE Foo (

    FooId  INT      NOT NULL,

    Bar    CHAR(30) NOT NULL,

    Baz    DATETIME NOT NULL,

    Qux    INT      NOT NULL,

    Corge  CHAR(25) NOT NULL,

    Grault INT      NOT NULL,

    Garply BIT      NOT NULL,

    Plugh  TEXT     NOT NULL,

    CONSTRAINT Foo_CK1 UNIQUE (FooId),          -- Single-column CANDIDATE KEY

    CONSTRAINT Foo_CK2 UNIQUE (Bar),            -- Single-column CANDIDATE KEY

    CONSTRAINT Foo_CK3 UNIQUE (Baz, Qux, Corge) -- Multi-column  CANDIDATE KEY

);

A candidate key set up as such is, as you know, susceptible of being the reference of one or more foreign key constraints.

How to portray an alternate key

If, on the contrary,

• a certain set of columns was chosen and defined as the PRIMARY KEY of a given table, and


• other set of columns is constrained as UNIQUE, and each of the members of such set is also fixed with a NOT NULL constraint,

then the designer is representing an alternate key (if, a certain table has one or more candidate keys, and one of these is granted the status of primary, then the remaining ones become alternate keys).

For instance, the following table presents one PRIMARY KEY and three ALTERNATE KEYs:

CREATE TABLE Foo (

    FooId  INT,

    Bar    CHAR(30) NOT NULL,

    Baz    DATETIME NOT NULL,

    Qux    INT      NOT NULL,

    Corge  CHAR(25) NOT NULL,

    Grault INT      NOT NULL,

    Garply BIT      NOT NULL,

    Plugh  TEXT     NOT NULL,

    CONSTRAINT Foo_PK  PRIMARY KEY (FooId),           -- Single-column PRIMARY KEY

    CONSTRAINT Foo_AK1 UNIQUE      (Bar),             -- Single-column ALTERNATE KEY

    CONSTRAINT Foo_AK2 UNIQUE      (Baz, Qux, Corge), -- Multi-column  ALTERNATE KEY

    CONSTRAINT Foo_AK3 UNIQUE      (Grault)           -- Single-column ALTERNATE KEY

);

A candidate key put up as demonstrated above can be, evidently, referenced from one or more foreign key constraints.

Illustration of the same table (i) with one candidate key and (ii) with primary key

Yes, at the logical level of abstraction of a database, a candidate key that is established by means of a table-level UNIQUE constraint in conjunction with the applicable column-level NOT NULL counterpart(s), as exemplified as follows:

CREATE TABLE Foo (

    FooId  INT      NOT NULL, 

    Bar    CHAR(30) NOT NULL,

    Baz    DATETIME NOT NULL,

    CONSTRAINT Foo_CK UNIQUE (FooId)

);

…would be equivalent to a primary key set up as shown below:

CREATE TABLE Foo (

    FooId  INT,

    Bar    CHAR(30) NOT NULL,

    Baz    DATETIME NOT NULL,

    CONSTRAINT Foo_PK PRIMARY KEY (FooId) -- Single-column PRIMARY KEY, constraint that implies the rejection of NULL marks.

);

This is so because, if a given table has only one candidate key (which can be composite, i.e., made up of two or more columns), then it can be considered, automatically, the primary key.

Physical-level support

And, yes, in order to support a UNIQUE constraint, some DBMSs may employ an index whose type is different from the one of the index utilized to sustain a PRIMARY KEY counterpart (e.g., non-clustered vs clustered), but this is a factor that is part of the physical (or internal) level of abstraction (which, by the way, might or might not apply depending of the DBMS of use) therefore it is entirely outside of the scope of the logical level constraints configured for a table (as long as the DBMS guarantees the uniqueness of the values contained in the column[s] involved).