Re: sql wrong? missing left parenthesis error?

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Mon, 18 Nov 2002 18:41:57 GMT
Message-ID: <VvaC9.282$wt7.22136959@newssvr14.news.prodigy.com>

Sandy wrote:
> I can't see anything wrong with this sql statement but SQL*Plus
> doesn't seem to like it. Anyone else see something wrong?
>
> *****************************
> SQL> ed
> Wrote file afiedt.buf
>
> 1 CREATE TABLE kodUserSession (
> 2 SessionID
> 3 UserID
> 4 foreign key User
> 5 LogonStamp
> 6 LogoffStamp
> 7 IPAddress
> 8 constraint kodUserSession_PK PRI
> 9 )
> 10* tablespace KOD_DATA
> 11 /
> foreign key User
> *
> ERROR at line 4:
> ORA-00906: missing left parenthesis
> *******************************************************
You can find the correct syntax for a CREATE TABLE statement in the Oracle docs (SQL Ref Manual) at http://tahiti.oracle.com.

You'll find that your statement should be something like:

CREATE TABLE kodUserSession (

   SessionID      number    not null
     constraint kodUserSession_PK
     primary key (SessionID),
   UserID         number    not null
     constraint kodUserSession_kodUser_FK
     foreign key (UserID)
     references kodUser (UserID)
     on delete cascade,
   LogonStamp     date,
   LogoffStamp    date,
   IPAddress      varchar2(16),

)
tablespace KOD_DATA
/

Couple of notes:

1. Table and column names in Oracle are not case-sensitive. You can refer to kodUserSession as kodusersession, which sorta negates the use of initial caps on words.
2. To create a foreign key (FK) as part of the create table statement, you have to make sure the referenced table already exists. It can sometimes be difficult to order the create statements so that this is always true. I prefer to save the creation of FKs until *AFTER* all of the tables have been created. Then issue a command such as:

ALTER TABLE kodUserSession

   ADD CONSTRAINT kodUserSession_kodUser_FK    FOREIGN KEY (UserID)
   REFERENCES kodUser (UserID)
/ Received on Mon Nov 18 2002 - 12:41:57 CST