5

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?

  • 2
    AFAIK: The word Candidate key has no meaning in any SQL DBMS. The set of UNIQUE constraints and the PRIMARY key is the CANDIDATE keys. I have never really understood why Primary keys where invented in the first place, i.e. why one of the CANDIDATE keys is considered special, and in need of a special name. – Lennart Jan 30 at 8:36
  • @Lennart I have never really understood why Primary key ... is considered special I personally understand the difference between a unique and a primary key this way. Unique key provides key expression uniqueness over current table data. Primary key provides key expression uniqueness over all table lifetime data. If a record with some unique combination was deleted, another record with that expression value can be inserted later. If a record with some primary key value was deleted, the creation of new record with that primary key value is at least wrong practice. And forbidden is better. – Akina Jan 30 at 8:44
  • those are good points. @Akina I dont know whether "...is at least wrong practice" is part of standard definition of primary key. I believe its more sort of recurring real world scenario or de facto practice. – anir Jan 30 at 8:49
  • 1
    @Lennart: the difference between a primary key and a unique key is that a primary key by definition does not allow NULL values, a unique key however might allow them. – a_horse_with_no_name Jan 30 at 9:59
  • 2
    @Kondybas: a table is never "ordered" by anything. Only the result of a query can be ordered. An neither the PK or a UK define any ordering to begin with. They only provide a way to uniquely identify a single row. – a_horse_with_no_name Jan 30 at 10:13
7

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 in this manner 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 true candidate key (besides, there are reasons to argue that 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 any attempt to insert NULL marks in 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
);

An alternate 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).

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.