Re: ORA-00905: missing keyword Need advice

"FutureZone" <bluestar77_at_walla-dot-com.no-spam.invalid> wrote in message news:4353ab9f$0$41000$892e7fe2_at_authen.white.readfreenews.net...
> Hi all,
>
> I've got this error when I create a table called "Offering" in
> SQL*Plus as follow:
>
> CREATE TABLE Offering( OfferNo INTEGER NOT NULL,
> CourseNo CHAR(6) NOT NULL,
> OffLocation VARCHAR(50),
> OffDays CHAR(6),
> OffTerm CHAR(6) NOT NULL,
> OffYear INTEGER NOT NULL,
> FacSSN CHAR(11),
> OffTime DATE,
> CONSTRAINT PKOffering PRIMARY KEY(OfferNo),
> CONSTRAINT FKCourseNo FOREIGN KEY(CourseNo) REFERENCES
> Course ON DELETE NO ACTION
> ON UPDATE NO ACTION,CONSTRAINT FKFacSSN FOREIGN
> KEY(FacSSN) REFERENCES Faculty
> ON DELETE SET NULL ON UPDATE CASCADE)
> /
>
> But the error saying missing key... What's wrong with my code? Any
> guidance? Thnks :)
>

This is mySQL syntax. In Oracle the only allowed syntax is ON DELETE CASCADE or ON DELETE SET NULL. NO ACTION is the behaviour by default. Didn'y try it but the following should work:

CREATE TABLE Offering( OfferNo INTEGER NOT NULL, CourseNo CHAR(6) NOT NULL,
OffLocation VARCHAR(50),
OffDays CHAR(6),
OffTerm CHAR(6) NOT NULL,
OffYear INTEGER NOT NULL,
FacSSN CHAR(11),
OffTime DATE, CONSTRAINT PKOffering PRIMARY KEY(OfferNo),

       CONSTRAINT FKCourseNo FOREIGN KEY(CourseNo) REFERENCES Course,
CONSTRAINT FKFacSSN FOREIGN KEY(FacSSN)
REFERENCES Faculty ON DELETE SET NULL)
/ Received on Tue Oct 18 2005 - 06:52:53 CDT