Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2013
    Posts
    57

    Unanswered: Constraint check

    The problem:
    Add a check constraint to number of students to ensure capacity is between 12 and 25 students. WARNING! Before attempting this, update records if necessary so table data meets this rule.

    The table:
    Code:
    SQL> select * from bw_class;
    
    CLASS CLASS_NAME           PROFESSOR          NUMBER_OF_STUDENTS       COST START_DAT END_DATE  ROO
    ----- -------------------- ------------------ ------------------ ---------- --------- --------- ---
    PC102 Peripherals          Henry Higgins                      14  $1,100.00 11-JAN-13 11-MAY-13 129
    PC101 MS OFFICE BASICS     INDIANA JONES                      18  $1,000.00 10-JAN-13 10-MAY-13 127
    EE101 Elementary Education Frank McCourt                      22    $900.00 12-JAN-13 12-MAY-13 227
    PC123 MS OFFICE ADVANCED   Bill Gates                         10    $800.00 13-JAN-13 13-MAY-13 180


    What I've tried:
    Code:
    SQL> ALTER TABLE BW_CLASS
      2  add CONSTRAINT class_size CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25);
    add CONSTRAINT class_size CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25)
                   *
    ERROR at line 2:
    ORA-02293: cannot validate (STUDENT.CLASS_SIZE) - check constraint violated
    Ok, this one I understand, 'Class_size' is a column in another table. This was my bad.

    Code:
    SQL> ALTER TABLE BW_CLASS
      2  add CONSTRAINT in_class CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25);
    add CONSTRAINT in_class CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25)
                   *
    ERROR at line 2:
    ORA-02293: cannot validate (STUDENT.IN_CLASS) - check constraint violated
    I don't get this. The other table is named students, and it has no 'IN_CLASS column.


    Code:
    SQL> ALTER TABLE BW_CLASS
      2  CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25);
    CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25)
    *
    ERROR at line 2:
    ORA-01735: invalid ALTER TABLE option
    Code:
    SQL> ALTER TABLE BW_CLASS
      2  ADD CHECK( NUMBER_OF_STUDENTS > 15  AND
      3  NUMBER_OF_STUDENTS < 25);
    ALTER TABLE BW_CLASS
    *
    ERROR at line 1:
    ORA-02293: cannot validate (STUDENT.SYS_C007537) - check constraint violated
    Ive done searches, looked in the textbook, etc. Im not sure exactly what im supposed to do here. Any clues?
    Last edited by bwilson95; 10-28-13 at 00:06. Reason: Forgot to add last attempt

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> CREATE TABLE FOOBAR (ID NUMBER);
    
    Table created.
    
    SQL> ALTER TABLE FOOBAR ADD (CONSTRAINT ck1 CHECK (ID between 12 and 25));
    
    Table altered.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by bwilson95 View Post
    What I've tried:
    Code:
    SQL> ALTER TABLE BW_CLASS
      2  add CONSTRAINT class_size CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25);
    add CONSTRAINT class_size CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25)
                   *
    ERROR at line 2:
    ORA-02293: cannot validate (STUDENT.CLASS_SIZE) - check constraint violated
    Ok, this one I understand, 'Class_size' is a column in another table. This was my bad.

    Code:
    SQL> ALTER TABLE BW_CLASS
      2  add CONSTRAINT in_class CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25);
    add CONSTRAINT in_class CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25)
                   *
    ERROR at line 2:
    ORA-02293: cannot validate (STUDENT.IN_CLASS) - check constraint violated
    I don't get this. The other table is named students, and it has no 'IN_CLASS column.

    ...

    Any clues?
    Both reported identifiers are names of the constraint you tried to create (note that the check constraint may contains checks on multiple columns).
    So, the reason is still the same: the constraint (CLASS_SIZE or IN_CLASS) in schema STUDENT is violated (the table has noncomplying values).
    http://ora-02293.ora-code.com/

    Maybe you should find the noncomplying rows manually:
    Code:
    select * from bw_class where not (NUMBER_OF_STUDENTS BETWEEN 12 AND 25);
    and make proper action (delete them, not validate the constraint) afterwards.

  4. #4
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    Code:
    SQL> CREATE TABLE FOOBAR (ID NUMBER);
    
    Table created.
    
    SQL> ALTER TABLE FOOBAR ADD (CONSTRAINT ck1 CHECK (ID between 12 and 25));
    
    Table altered.
    Thanks.
    This is for a table already already in the database; it was created last week.
    Still I havent seen code quite like what you have there, Ill try it now....

  5. #5
    Join Date
    Sep 2013
    Posts
    57
    Code:
    SQL> ALTER TABLE BW_CLASS (CONSTRAINT ck1 CHECK (NUMBER_OF_STUDENTS between 12 and 25));
    ALTER TABLE BW_CLASS (CONSTRAINT ck1 CHECK (NUMBER_OF_STUDENTS between 12 and 25))
                         *
    ERROR at line 1:
    ORA-01735: invalid ALTER TABLE option

    what is the ck1 ? do i need to dig into the DB deeper for a value of some sort?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by bwilson95 View Post
    Code:
    SQL> ALTER TABLE BW_CLASS (CONSTRAINT ck1 CHECK (NUMBER_OF_STUDENTS between 12 and 25));
    ALTER TABLE BW_CLASS (CONSTRAINT ck1 CHECK (NUMBER_OF_STUDENTS between 12 and 25))
                         *
    ERROR at line 1:
    ORA-01735: invalid ALTER TABLE option

    what is the ck1 ? do i need to dig into the DB deeper for a value of some sort?

    problem exists between keyboard and chair.
    your ALTER throws error because of invalid syntax.
    You need to drop this class since you can not apply a working example to your specifics.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    Code:
    SQL> CREATE TABLE FOOBAR (ID NUMBER);
    
    Table created.
    
    SQL> ALTER TABLE FOOBAR ADD (CONSTRAINT ck1 CHECK (ID between 12 and 25));
    
    Table altered.
    ok, got it to work, thanks!

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    please post your working SQL
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    please post your working SQL
    Code:
    SQL> ALTER TABLE BW_CLASS
      2  add CONSTRAINT in_class CHECK (NUMBER_OF_STUDENTS BETWEEN 12 AND 25);
    Didnt work before b/c I forgot I had a value of '10' in there.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You're welcome
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  11. #11
    Join Date
    Sep 2013
    Posts
    57
    Quote Originally Posted by anacedent View Post
    You're welcome
    Yes, Thank You, again.

Posting Permissions

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