Ygthkb

I know the concept of candidate key in RDBMS theory, but do candidate keys really exist in actual SQL engines? I mean is there any way to designate a particular column or set of columns as a candidate key in any of the SQL database management systems, say SQL Server, Postgres, MySQL, Oracle etc.?

Is there any reserved keyword for designating column(s) as a candidate key like PRIMARY KEY or UNIQUE in case of primary key column and unique column?

I feel UNIQUE constraint itself provides implementation of the candidate key concept. I don't see any practical value of having a 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 (as I consider that you are suggesting in the question) 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.

It is worth to stress the fact that, since SQL and its dialects include the idea of NULL marks, a UNIQUE constraint alone is not sufficient to stand for a candidate key (as expounded in the DDL sample above). This point is particularly significant because the column(s) constrained as a candidate key cannot retain NULL marks, otherwise it could not be deemed a real candidate key (besides, a table enclosing NULL marks in any of its columns cannot be considered a relational table but, yes, that is a different subject).

How does this differ from the CANDIDATE KEY keyword approach?

In this way, if the vendors/developers of a certain SQL DBMS wants to provide the CANDIDATE KEY keyword, then this kind of constraint, apart from the evident uniqueness enforcement, must also ensure the rejection of NULL marks in the relevant column(s), factor that would make it different from the approach combining the UNIQUE and NOT NULL constraint(s).

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.

Using the CANDIDATE KEY keyword when there is already a PRIMARY KEY?

Assuming that there is a DBMS that does provide the CANDIDATE KEY keyword, if a table has a primary key declared, then the creation of a candidate key should be rejected, and said DBMS should as well provide the ALTERNATE KEY keyword to represent one or more alternate keys when applicable.

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, as illustrated before, 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).

I'll go out on a limb and guess you've done a course based on Elmasri and Navathe's textbook. This book is quite theoretical and isn't the clearest of prose, and it tends to use terminology that isn't widely used elsewhere. The net effect it that it seems to confuse students on a semi-regular basis - many if not most questions like this seem to originate from students trying to make sense of this textbook.

In data modelling, a candidate key is a possible key that you could use to identify a record. There may be just one or more than one; some could be single references, some could be combinations of attributes. It is purely a modelling concept to describe keys that you could select to use as a primary identifier.

Tables in a RDBMS can only have a single primary key (selected at design time) but they can have multiple uniqueness constraints. Normally you would select a key (or add a synthetic key) and use that as the primary identifier. You can also create uniqueness constraints on column(s) that would make up other candidate keys, although these are not called candidate keys when physicalised in this way.