Re: column_constraint_clause

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 3 Aug 1999 08:25:25 GMT
Message-ID: <7o691l$ro3$1@news.seed.net.tw>

kev <kevin.porter_at_fast.no> wrote in message news:37A5D71B.93D620D6_at_fast.no...
> Hi,
>
> I was wondering why I get an error with this create table statement:
>
> create table country
> (
> code varchar2(5) primary key,
> cont varchar2(5) foreign key references continent (code),
> name varchar2(30) not null
> );
>
> The error I get is:
>
> cont varchar2(5) foreign key references continent (code),
> *
> ORA-00907: missing right parenthesis
>
>
> I'm trying to make cont a foreign key which references the code column
> of the continent table.
> Is 'foreign key references continent (code),' not a valid
> column_constraint_clause' (as described on p.379 of the Oracle
> DB Administration book by O'Reilly). Is it in the wrong place? I noticed
> that in the $ORACLE_HOME/rdbms/demo/summit2.sql script, foreign keys are
> added using the ALTER TABLE command after the tables have been created.
> Why is this? What is the usual way of specifying foreign keys?

The words "foreign key" are needless in column constraints. However, they are mandatory in table constraints. Try this:

create table country
(

        code varchar2(5) primary key,
        cont varchar2(5) references continent (code),
        name varchar2(30) not null

);

or

create table country
(

        code varchar2(5) primary key,
        cont varchar2(5),
        name varchar2(30) not null,
        foreign key (cont) references continent (code)

); Received on Tue Aug 03 1999 - 03:25:25 CDT