Oracle 10g SQL Statement Help

[gregor002]

I'm creating some tables in ORACLE 10g. I have a table called FACULTY that has a primary key called FID. I'm trying to create a table called COURSE that has a column that references FACULTY's primary key. The column in COURSE is a foreign key. Here is the SQL:

CREATE TABLE COURSE(
       CRSNBR    char(6)
                 CONSTRAINT pk_CRSNBR PRIMARY KEY,
       CNAME     char(20)
                 CONSTRAINT nn_CSNAME NOT NULL,
       CREDIT    char(1),
       MAXENRL   smallint,
       FID       char(3)
                 CONSTRAINT fk_FID FOREIGN KEY
                 REFERENCES FACULTY(FID)
);

I get the following error: Error at line 9 (bolded above) - ORA-02253: Constraint specification not allowed here.

Huh? But the FACULTY table is already created!? Ideas?

Thanks!

[r937]

haven't used oracle in a long time, but i think what you have there is an inline constraint (the clause is actually part of the FID column), and as such, you don't need the FOREIGN KEY keywords

inline:

CREATE TABLE COURSE(
       CRSNBR    char(6)
                 CONSTRAINT pk_CRSNBR PRIMARY KEY,
       CNAME     char(20)
                 CONSTRAINT nn_CSNAME NOT NULL,
       CREDIT    char(1),
       MAXENRL   smallint,
       FID       char(3)
                 CONSTRAINT fk_FID 
                 REFERENCES FACULTY(FID)  )

out of line:

CREATE TABLE COURSE(
       CRSNBR    char(6)
                 CONSTRAINT pk_CRSNBR PRIMARY KEY,
       CNAME     char(20)
                 CONSTRAINT nn_CSNAME NOT NULL,
       CREDIT    char(1),
       MAXENRL   smallint,
       FID       char(3)
   , CONSTRAINT fk_FID FOREIGN KEY (FID) 
                 REFERENCES FACULTY(FID)  )

[gregor002]

Thanks for the the reply. This seemed to solve it:

SQL> CREATE TABLE COURSE(
  2         CRSNBR    char(6)
  3                   CONSTRAINT pk_CRSNBR PRIMARY KEY,
  4         CNAME     char(20)
  5                   CONSTRAINT nn_CSNAME NOT NULL,
  6         CREDIT    char(1),
  7         MAXENRL   smallint,
  8         FID       char(3)
  9  );

Table created.

SQL> ALTER TABLE COURSE ADD CONSTRAINT fk_FID
  2        FOREIGN KEY(FID)
  3            REFERENCES FACULTY(FID);

Table altered.

Definitley an inline issue.