Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2005
    Posts
    13

    Talking Unanswered: SQL PLUS Check Constraints

    Hi Guys, Hope this is the correct forum!

    I am using SQL PLUS in Oracle

    Is anybody able to help me with the following please, I have looked everywhere:

    Check Constraints:

    --Adding a constraint to check if the date entered into the date field is greater then or equal to SYSDATE? I believe you cannot use....
    check(date >=SYSDATE) for some reason??

    --ensuring that the job field is equal to cleaner, teacher or manager

    Any help is greatly appreciated,
    Many Thanks,

    baby_boomerang

  2. #2
    Join Date
    Nov 2005
    Posts
    17
    If the job field is character data then the check constraint can be used like so CHECK(UPPER(job_field) IN ('CLEANER', 'TEACHER', 'MANAGER'))

    Otherwise, use a FOREIGN KEY constraint if it refers to an ID in another table.

    As for the date field, you can use a trigger:

    CREATE OR REPLACE TRIGGER <name>
    BEFORE INSERT OR UPDATE ON <date_field>
    ON <table_name>
    FOR EACH ROW
    BEGIN
    IF :NEW.<date_field> < SYSDATE THEN
    RAISE_APPLICATION_ERROR(....); -- Be careful as this causes rollback!!!
    END IF;
    END;
    /

  3. #3
    Join Date
    Nov 2005
    Posts
    13

    Talking

    Thanks very much for your reply, its very helpful!

    What do you mean by rollback? (Sorry Im new to all this stuff)

    Thanks,
    BB

  4. #4
    Join Date
    Nov 2005
    Location
    Gex - France
    Posts
    15
    Hi baby_boomerang,



    When you submit a DML (Data Modification Language) statement, Oracle does the job in a temporary space. You can see it, but not other users.

    When you are sure that your statements are good you COMMIT the transaction. This tells Oracle to make your modifications public.

    If you discover an issue, you want to cancel your transaction. Here you ROLLBACK your job.

  5. #5
    Join Date
    Nov 2005
    Posts
    13

    checks

    In the above post what does the upper bit mean?

    CHECK(UPPER...

    Thanks
    BB

  6. #6
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212
    UPPER() is function which returns chars (input) in uppercase.
    Code:
    select upper('abc123') from dual;
    result is
    Code:
    ABC123
    I guess
    Code:
    CHECK(UPPER(job_field) IN ('CLEANER', 'TEACHER', 'MANAGER'))
    means: to check if job_field is one of the following:
    cleaner,
    teacher,
    manager

    even I don't know function CHECK
    Last edited by madafaka; 11-20-05 at 20:16.

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I don't even know function CHECK
    CHECK is part of the syntax of a constraint definition. For example:

    Code:
    SQL> CREATE TABLE testit (testcol VARCHAR2(1));
    
    Table created.
    
    SQL> ALTER TABLE testit 
      2  ADD CONSTRAINT test_chk CHECK(testcol < 'C');
    
    Table altered.
    
    SQL> INSERT INTO testit (testcol) VALUES ('A');
    
    1 row created.
    
    SQL> UPDATE testit SET testcol = 'B';
    
    1 row updated.
    
    SQL> UPDATE testit SET testcol = 'Z';
    UPDATE testit SET testcol = 'Z'
    *
    ERROR at line 1:
    ORA-02290: check constraint (WILLIAMR.TEST_CHK) violated
    The check constraint only allowed the column to hold values < 'C'.

    As with other types of column constraint, you can also define them inline as part of the table definition:

    Code:
    SQL> DROP TABLE testit;
    
    Table dropped.
    
    SQL> CREATE TABLE testit                     
      2  ( testcol VARCHAR2(1) CONSTRAINT test_chk CHECK(testcol < 'C') );
    
    Table created.
    You don't even have to name them, although it's good practice to do so (for one thing, a name can make any constraint violation error messages more meaningful):

    Code:
    SQL> DROP TABLE testit;
    
    Table dropped.
    
    SQL> CREATE TABLE testit  
      2  ( testcol VARCHAR2(1) CHECK(testcol < 'C') );
    
    Table created.
    
    SQL> SELECT constraint_name, constraint_type, search_condition
      2  FROM   user_constraints
      3  WHERE  table_name = 'TESTIT';
    
    CONSTRAINT_NAME      C SEARCH_CONDITION
    -------------------- - --------------------------------------------------------------------------------
    SYS_C002067          C testcol < 'C'
    
    1 row selected.

  8. #8
    Join Date
    Nov 2005
    Posts
    13
    Thanks Guys.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by cmerry
    RAISE_APPLICATION_ERROR(....); -- Be careful as this causes rollback!!!
    ... just like a violated CHECK or FOREIGN KEY constraint does. The triggering statement gets rolled back (undone), and the calling program can choose whether or not to roll back the whole transaction prior to the statement.

  10. #10
    Join Date
    Sep 2011
    Location
    Hyderabad
    Posts
    18

    vithal reddy

    how to apply check constraint on number data type to restrict decimal values?
    ex;;
    create table ex(sno number(2));

    alter table ex add primary key(sno);
    now i am inserting decimal values it is going to insert example
    insert into ex values(12.53);
    1 row inserted
    but i want to restrict only numbers allowed not float values.
    to do this one check constraint?

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could use:

    Code:
    check (sno = trunc(sno))

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    As the SNO column has NUMBER(2) data type, it cannot contain numbers with decimal part. When inserting a non-integer value into it, its decimal part may be removed (I am not sure whether it was truncated or rounded, but you can see it after running what you posted). What about checking the entered value in your front end (whatever it is)?

    Another option would be to change the type of that column to NUMBER(3,1) and use andrewst's constraint.

Posting Permissions

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