SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Awesome Addict
    Join Date
    Mar 2004
    Location
    Toronto, Canada
    Posts
    326
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Oracle 10g SQL Statement Help

    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:

    Code:
    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!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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:
    Code:
    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:
    Code:
    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)  )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Awesome Addict
    Join Date
    Mar 2004
    Location
    Toronto, Canada
    Posts
    326
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the the reply. This seemed to solve it:
    Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •