Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2012
    Posts
    9

    Question Unanswered: date check error please help!

    so i have to make this table for school including a birth date that must be before 1995.
    i got this so far but i get an error;
    http://i46.tinypic.com/35m19j8.png
    what am i doing wrong?

    thanks for advice

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I can't open files; could you, rather, post copy/paste of your SQL*Plus session?

  3. #3
    Join Date
    Sep 2012
    Posts
    9
    CREATE TABLE RoyteDorsthorst (
    studentnummer number(6) primary key,
    naam varchar2(20) not null,
    mobiele_nummer char(10),
    geb_datum date
    CONSTRAINT chk_geb_datum CHECK (geb_datum<01-JAN-1995),
    foto blob
    );



    Error at Command Line:6 Column:54
    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 constriant.

  4. #4
    Join Date
    Sep 2012
    Posts
    9
    Quote Originally Posted by Littlefoot View Post
    I can't open files; could you, rather, post copy/paste of your SQL*Plus session?
    sorry it took so long but i had life science class

  5. #5
    Join Date
    Sep 2012
    Posts
    5

    @roydorsthorst date check error please help!

    Don't create a column level constraint. Create a table level constraint for get_datum field.

    Code:



    CREATE TABLE RoyteDorsthorst(
    studentnumber number(6) primary key,
    naam varchar2(20) not null,
    mobiele_nummer char(10),
    get_datum date,
    CONSTRAINT chk_geb_datum CHECK(geb_datum<'01-JAN-95'),
    foto blob
    );

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    '01-JAN-95' is a string, and it is a bad idea to compare dates with strings.

    Here's how I'd put it:
    Code:
    SQL> create table roytedorsthorst
      2    (studentnumber  number(6)    primary key,
      3     naam           varchar2(20) not null,
      4     mobiele_nummer char(10),
      5     get_datum      date,
      6     foto           blob,
      7                                 constraint chk_geb_datum
      8                                   check (get_datum < to_date('01.01.1995', 'dd.mm.yyyy'))
      9   );
    
    Table created.
    
    SQL>

  7. #7
    Join Date
    Sep 2012
    Posts
    9
    alright worked both thank you guys!
    so stupid that i just missed this little thing, but hey im learning

    cheers

Posting Permissions

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