How do I specify a candidate key and a foreign key when creating a table in Oracle 10g?

How do I开发者_StackOverflow中文版 specify a candidate key and a foreign key when creating a table in Oracle 10g?

Following on from rics:

CREATE TABLE supplier
(   supplier_id     numeric(10)     not null,
    supplier_name   varchar2(50)    not null,
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id),
    CONSTRAINT supplier_unique_name UNIQUE (supplier_name)
);

CREATE TABLE supplier_parts
(   supplier_id     numeric(10)     not null,
    part_name       varchar2(50)    not null,
    CONSTRAINT supplier_id_fk FOREIGN KEY (supplier_id)
       REFERENCES supplier (supplier_id)  
);

CREATE TABLE silly
(   supplier_name   varchar2(50),
    CONSTRAINT supplier_name_fk FOREIGN KEY (supplier_name)
       REFERENCES supplier (supplier_name)  
);

In the above example, supplier_pk is a Primary key. supplier_pk and supplier_unique_name are candidate keys. supplier_id_fk and supplier_name_fk are Referential Integrity constraints (or Foreign keys).

A candidate key is a key that uniquely identifies rows in a table. It can be used as the primary key of the table. For example:

CREATE TABLE supplier
(   supplier_id     numeric(10)     not null,
    supplier_name   varchar2(50)    not null,
    contact_name    varchar2(50),   
    CONSTRAINT supplier_pk PRIMARY KEY (supplier_id)
);