Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2004
    Location
    Mumbai
    Posts
    5

    Candidate key and unique key

    Is there any difference between,

    Candidate key, unique key and a primary key...?

    Is there any difference between these?

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    "Candidate key" is a term from relational database theory, meaning any potential key for a table (primary or not). The term is not used in Oracle.

    "Primary Key" and "Unique" are two kinds of constraint supported by Oracle and other SQL databases:

    Primary Key: a table can only have one Primary Key, which cannot contain any nullable columns. Every row in the table is guaranteed to have unique values in the primary key columns.

    Unique: a table can have any number of unique constraints. Unique constraints are not true keys, because they can contain nulls. Uniqueness is enforced only when values are not null.

  3. #3
    Join Date
    Apr 2004
    Location
    Mumbai
    Posts
    5
    Thanks Andrew,

    Now tell me.. What is the difference between alrernate key and a candidate key ...

    Is alternate key a mere cocept??? Or can we simulate such one in oracle?

    What I understand from alternate key is that... "A candidate key which uniquely identifies the record, apart from the primary key".

    How can we have two different keys in a table??

    Regards,
    Amjath

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    "Alternate" again comes from the theory - it is any candidate key apart from the primary key. It is similar to SQL's Unique constraint, except that it is a true key (i.e. cannot contain nulls). So yes, you can simulate an alternate key in Oracle using a combination of NOT NULL and UNIQUE constraints.

    A table can easily have 2 or more candidate keys: for example, consider this table for hotel room reservations:

    CREATE TABLE reservation
    ( reservation_no INTEGER NOT NULL
    , room_no INTEGER NOT NULL
    , from_date DATE NOT NULL
    , to_date DATE NOT NULL
    );

    There are 3 candidate keys here - i.e. three sets of columns whose values must always be unique to a single row:

    1) reservation_no

    2) room_no, from_date

    3) room_no, to_date

    If we choose reservation_no to be the primary key, then the other 2 are alternate keys. (But we could choose any of the 3 as the primary).

    In Oracle, we would define these 3 keys like this:

    ALTER TABLE reservation ADD
    ( CONSTRAINT res_pk PRIMARY KEY (reservation_no)
    , CONSTRAINT res_uk1 UNIQUE (room_no, from_date)
    , CONSTRAINT res_uk2 UNIQUE (room_no, to_date)
    );

  5. #5
    Join Date
    Apr 2004
    Location
    Mumbai
    Posts
    5
    Andrew,

    Thanks for the quick reply.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •