Does the concept of candidate key exist only in theory

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