If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Candidate key and unique key

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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?
Reply With Quote
  #2 (permalink)  
Old
Moderator.
 
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.
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Moderator.
 
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)
);
__________________
Tony Andrews
http://tinyurl.com/tonyandrews
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Mumbai
Posts: 5
Andrew,

Thanks for the quick reply.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On