8 Replies Latest reply: Dec 4, 2012 3:32 PM by 977973 RSS

    when I try to create table Application ,I am getting an error message .

    977973
      create table College(cName varchar2(200) NOT NULL PRIMARY KEY,
                state varchar2(20) NOT NULL,
           enrollment int NOT NULL (enrollment > 0);


      create table Student(sID int NOT NULL, sName varchar2(200) NOT NULL,
      GPA real NOT NULL CHECK(gpa >0 AND gpa < 4.0),
                sizeHS int NOT NULL,
                CONSTRAINT Primary_Key_SID PRIMARY KEY (SID));

      create table Application(sID int NOT NULL,
      cName varchar2(200) NOT NULL,
                     major varchar2(20) NOT NULL,
           decision varchar2(10)) NOT NULL CHECK(decision ='y'OR decision = 'n');
                     CONSTRAINT fk_Sid FOREIGN KEY (SID) REFERENCES Student(SID),
                     CONSTRAINT fk_Cname FOREIGN KEY(CNAME) REFERENCES college(CNAME));


      Error starting at line 5 in command:
      CONSTRAINT fk_Sid FOREIGN KEY (SID) REFERENCES Student(SID),
      Error report:
      Unknown Command

      Error starting at line 6 in command:
      CONSTRAINT fk_Cname FOREIGN KEY(CNAME) REFERENCES college(CNAME))
      Error report:
      Unknown Command

      Edited by: 974970 on Dec 4, 2012 12:54 PM
        • 1. Re: when I try to create table Application ,I am getting an error message .
          sb92075
          974970 wrote:
          create table College(cName varchar2(200) NOT NULL,
                    state varchar2(20) NOT NULL,
               enrollment int NOT NULL (enrollment > 0);


          create table Student(sID int NOT NULL, sName varchar2(200) NOT NULL,
          GPA real NOT NULL CHECK(gpa >0 AND gpa < 4.0),
                    sizeHS int NOT NULL,
                    CONSTRAINT Primary_Key_SID PRIMARY KEY (SID));

          *create table Application(sID int NOT NULL,
          cName varchar2(200) NOT NULL,
                         major varchar2(20) NOT NULL,
               decision varchar2(10)) NOT NULL CHECK(decision ='y'OR decesion = 'n');
          typo in line above? decesion or decision?
          • 2. Re: when I try to create table Application ,I am getting an error message .
            Frank Kulash
            Hi,

            Welcome to the forum!
            974970 wrote:
            create table College(cName varchar2(200) NOT NULL,
                      state varchar2(20) NOT NULL,
                 enrollment int NOT NULL (enrollment > 0);


            create table Student(sID int NOT NULL, sName varchar2(200) NOT NULL,
            GPA real NOT NULL CHECK(gpa >0 AND gpa < 4.0),
                      sizeHS int NOT NULL,
                      CONSTRAINT Primary_Key_SID PRIMARY KEY (SID));

            create table Application(sID int NOT NULL,
            * cName varchar2(200) NOT NULL, *
                           * major varchar2(20) NOT NULL,*
                 * decision varchar2(10)) NOT NULL CHECK(decision ='y'OR decesion = 'n');*
                           * CONSTRAINT fk_Sid FOREIGN KEY (SID) REFERENCES Student(SID),*
                           * CONSTRAINT fk_Cname FOREIGN KEY(CNAME) REFERENCES college(CNAME));*
            Thanks for posting the CREATE TABLE statements; that's very helpful.
            Error at Command Line:6 Column:26
            Error report:
            SQL Error: ORA-02438: Column check constraint cannot reference other columns
            *02438. 00000 - "Column check constraint cannot reference other columns"*
            Cause:    attempted to define a column check constraint that references
            another column.
            Action:   define it as a table check constraint.

            Edited by: 974970 on Dec 4, 2012 12:39 PM
            Are you sure this is the code you're running? I would expect different errors, beacuse of the ";" after the last CHECK constraint.

            If a foreign key is going to reference college.cname, then college.cname must have a PRIMARY KEY or UNIQUE constraint.
            • 3. Re: when I try to create table Application ,I am getting an error message .
              977973
              I have removed the typos ,can you take a look again ?Thanks .
              • 4. Re: when I try to create table Application ,I am getting an error message .
                977973
                I have edited code can you find error ?Thanks.
                • 5. Re: when I try to create table Application ,I am getting an error message .
                  EdStevens
                  a couple of side observations ..

                  First, it is much easier to read code when it is (1) formatted, and (2) that formatting is preserved in the forum by use of the \
                   tag.
                  CREATE
                  TABLE College
                  (
                  cName VARCHAR2(200) NOT NULL
                  , state VARCHAR2(20) NOT NULL
                  , enrollment INT NOT NULL (enrollment > 0);
                  CREATE
                  TABLE Student
                  (
                  sID INT NOT NULL
                  , sName VARCHAR2(200) NOT NULL
                  , GPA REAL NOT NULL CHECK(gpa >0 AND gpa < 4.0)
                  , sizeHS INT NOT NULL
                  , CONSTRAINT Primary_Key_SID PRIMARY KEY (SID)
                  );
                  CREATE
                  TABLE Application
                  (
                  sID INT NOT NULL
                  , cName VARCHAR2(200) NOT NULL
                  , major VARCHAR2(20) NOT NULL
                  , decision VARCHAR2(10)
                  )
                  NOT NULL CHECK
                  (
                  decision ='y'OR decision = 'n'
                  );
                  CONSTRAINT fk_Sid FOREIGN KEY (SID) REFERENCES Student(SID),
                  CONSTRAINT fk_Cname FOREIGN KEY(CNAME) REFERENCES college(CNAME));
                  Now,
                  First, drop the mixed case names.  Oracle isn't going to store them in mixed case in the dictionary, and if your force it to do so you just create bigger problems for yourself.  You may have learned mixed case in the MS world, but this is Oracle.  When in Rome
                  
                  Second, you have a column named SID  (yes, you entered it as "sID", but to oracle it is SID.  And to oracle, it is a key word.  Find an alternative.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                  • 6. Re: when I try to create table Application ,I am getting an error message .
                    Frank Kulash
                    Hi,

                    You still have a ';' after the last CHECK constraint.

                    It helps if you format your code, to make it easier to catch errors like that.
                    Try this:
                    CREATE TABLE College
                    (   cName     VARCHAR2 (200)     PRIMARY KEY     -- Implies NOT NULL
                    ,   state      VARCHAR2 (20)      NOT NULL
                    ,   enrollment      INT            NOT NULL 
                                            CHECK     -- Keyword 'CHECK' needed here
                                                (enrollment > 0)
                    )     -- Another right ')' needed here
                    ;
                    
                    
                    CREATE TABLE     Student
                    (   sID      INT          NOT NULL
                    ,   sName     VARCHAR2 (200)      NOT NULL
                    ,   GPA          REAL           NOT NULL 
                                            CHECK (   gpa  > 0 
                                              AND gpa  < 4.0
                                              )
                    ,   sizeHS     INT          NOT NULL
                    ,   CONSTRAINT Primary_Key_SID PRIMARY KEY (SID)
                    )
                    ;
                    
                    
                    CREATE TABLE     Application
                    (   sID      INT          NOT NULL
                    ,   cName     VARCHAR2 (200)      NOT NULL
                    ,   major     VARCHAR2 (20)     NOT NULL
                    ,   decision     VARCHAR2 (1)       -- only 1 right ')' here
                                             -- If the only possible values are 'y' and 'n', then 
                                        --    it doesn't need to be 10 characters long
                                             NOT NULL CHECK (decision IN ('y', 'n'))
                    ,     -- ',' not ';' here
                      CONSTRAINT     fk_Sid           FOREIGN KEY (SID)   REFERENCES Student (SID)
                    , CONSTRAINT     fk_Cname      FOREIGN KEY (CNAME) REFERENCES college (CNAME)
                    )
                    ;
                    Pay attentiton to the comments.