Re: Foreign composite keys

From: Kirmo Uusitalo <kirmo.uusitalo_at_iki.fi.nospam>
Date: 1997/06/17
Message-ID: <33a6bdd6.126935663@news.kolumbus.fi>#1/1

On Tue, 17 Jun 1997 09:11:32 -0400, Gabe Goldhirsh <ggoldh1_at_triton.towson.edu> wrote:

>In ORACLE 7, what is the syntax for constructing a foreign composite
>key, so far I have: [and am still getting an error]
>DROP TABLE account;
>FOREIGN KEY (user_id)
>AND FOREIGN KEY (password)
>);

You can't use the inline syntax for multi-column constraints, so use the other variant:

CREATE TABLE account
(

server          VARCHAR2(15),
memory          VARCHAR2(15),
classification  VARCHAR2(15),
user_id         VARCHAR2(15),
password        VARCHAR2(15),

 constraint fk_account foreign key
(user_id,password) references some_other_table(uid,pwd);

as the referenced table some_other_table may be created later in the script, I prefer to add the FK constraints separately - that way you can run your create script twice to ensure that all tables are created properly.

CREATE TABLE account /* create statement without FK constraints */ (

server          VARCHAR2(15),
memory          VARCHAR2(15),
classification  VARCHAR2(15),
user_id         VARCHAR2(15),
password        VARCHAR2(15));

alter table account add constraint fk_account foreign key (user_id,password) references some_other_table(uid,pwd);

HTH, Kirmo Uusitalo

Kirmo.Uusitalo_at_iki.fi                   Key Technologies Oy 
System Analyst                          Uotinmaenkuja 9
tel. +358-9-323451,+358-500-439125      00970  HELSINKI
fax         324031                      FINLAND

Received on Tue Jun 17 1997 - 00:00:00 CDT