How do I specify a candidate key and a foreign key when creating a table in Oracle 10g?
views:
34answers:
2
A:
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)
);
rics
2010-09-19 21:10:11
yes, so what is the candidate key in your table, I want to specify it, but I don't know the syntax.
baboonWorksFine
2010-09-19 21:18:55
supplier_id - the list of fields in the parens after primary key- also you could add a unique constraint for another candidate key
Mark
2010-09-19 21:36:42
@baboonWorksFine: there is no "the" candidate key, a candidate key is just that - a column (or group of columns) that is a *candidate* for being the primary key.
Jeffrey Kemp
2010-09-24 23:57:20
+1
A:
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).
Jeffrey Kemp
2010-09-20 04:49:20