Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: SQL Create table bug

    hey i'm workin with creating tables in sql and
    i'm not sure where i'm syntactically incorrect,
    yeh i'm very new to sql.


    Code:
    SQL> CREATE TABLE Course_Enrollment
      2  (
      3   Student_ID char(8) NOT NULL,
      4   Course_ID char(10) NOT NULL,
      5   Year_enrolled char(4) NOT NULL,
      6   Semester Integer NOT NULL,
      7   Grade Integer DEFAULT NULL,
      8   PRIMARY KEY(Student_ID,Course_ID, Year_enrolled, Semester),
      9   FOREIGN KEY(Student_ID) REFERENCES Student(Student_ID) ON UPDATE CASCADE
     10   FOREIGN KEY(Course_ID) REFERENCES Course(Course_ID) ON UPDATE CASCADE
     11   CONSTRAINT GradeCheck CHECK (Grade IN (1,2,3,4,5,6,7))
     12  );
     FOREIGN KEY(Student_ID) REFERENCES Student(Student_ID) ON UPDATE CASCADE
                                                               *
    ERROR at line 9:
    ORA-00905: missing keyword

    Thanx

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Castiel
    hey i'm workin with creating tables in sql and
    i'm not sure where i'm syntactically incorrect,
    yeh i'm very new to sql.
    Anytime you are worried about syntax, please consult SQL Reference book. It is part of Oracle documentation, available e.g. on http://tahiti.oracle.com/.

    You would find there, that, although the statement seems syntactically correct, there is no ON UPDATE clause for reference constraint. This thread on AskTom describes it more: http://asktom.oracle.com/pls/asktom/...:5773459616034.

    The idea of joining tables on column, which may be changed, is not good. I would recommend you to change this design (e.g. by using surrogate key).
    Or maybe just remove that ON UPDATE clause. Do you expect that STUDENT_ID or COURSE_ID may change?

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    also please do not use CHAR, use VARCHAR2. it will cause you much less problems in the future. As a general rule, avoid CHAR and LONG columns.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by beilstwh
    also please do not use CHAR, use VARCHAR2. it will cause you much less problems in the future. As a general rule, avoid CHAR and LONG columns.
    QFT!

    I love that semester and grade are integers but YEAR is not.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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