Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Posts
    8

    Red face Unanswered: CHECK constraint not working ?

    Hi all,

    I am using Oracle SQL Developer Version 1.5.1
    and
    MySQL Server 5.0 as Database,

    I am trying to enforce a check constraint in a simple table, but it is not working as it should be.

    Code:
    CREATE TABLE EMPSAL
    (
    ID          INTEGER      NOT NULL,
    SALARY      INTEGER      CHECK (SALARY >= 15000)
    );
    Still I am able to insert values less than 15000.
    Code:
    INSERT INTO EMPSAL values (57, 1000);
    Code:
    SELECT * FROM EMPSAL
    ID          SALARY      
    ----------- ----------- 
    27          1000        
    57          1000
    Pls help .

    Thanks and Regards,
    VIKAS

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Another one of the "I pretend being a real DBMS but I won't let you know I ignore this until your data is corrupted" things in MySQL.

    Quote from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

    "The CHECK clause is parsed but ignored by all storage engines"

  3. #3
    Join Date
    Sep 2008
    Posts
    8

    Exclamation

    Quote Originally Posted by shammat
    Another one of the "I pretend being a real DBMS but I won't let you know I ignore this until your data is corrupted" things in MySQL.

    Quote from http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

    "The CHECK clause is parsed but ignored by all storage engines"
    Thanks for the reply BUT
    I am sorry, but could not understand it. I am totally new to SQL.

    Pls gimme the exact command, so that I can make note of it for future.

    I was just practicing examples in http://www.itl.nist.gov/div897/ctg/dm/sql_examples.htm


    Thanks in adv,
    VIKAS

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The CHECK clause is parsed but ignored by all storage engines.
    looks fairly clear to me.. none of the curently supported MySQL storage engnes support h a check constraint. so you cannot do what you want using a check constraint...... I suppose you could enforce such constraints with a stored procedure.

    incidentally I'm surprised you are using Oracle SQL Developer on a MySQL db... I'd of thought using the MySQL tools would make more sense
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2008
    Posts
    8

    Smile

    Quote Originally Posted by healdem
    looks fairly clear to me.. none of the curently supported MySQL storage engnes support h a check constraint. so you cannot do what you want using a check constraint...... I suppose you could enforce such constraints with a stored procedure.

    incidentally I'm surprised you are using Oracle SQL Developer on a MySQL db... I'd of thought using the MySQL tools would make more sense

    Thanks a ton, for the useful info. Will try MY SQL Tools.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by vikas027
    Will try MY SQL Tools.
    That won't solve your problem. MySQL simply does not support CHECK constraints.

    Quote Originally Posted by healdem
    incidentally I'm surprised you are using Oracle SQL Developer on a MySQL db.
    Oracle's SQL Developer started supporting other database with the 1.2 version (I think, could be 1.5 as well).
    I think the main reason why they do it, is for the migration tools they have built into SQL Developer

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in my books theres a lot to be said for using the tools provided by the same company as the DB you are using. I'd agree if you expect to use Oracle, the use the Oracle tool.

    I don't know the oracle tool, but the MySQL query browser in my books has a lot of neat features which integrate well with the server. not least is the help and SQL reference which is a boon to people just starting out on the SQL trial.

    I used to find administering MySQL Db's a pain until I came accross MySQL Adminstrator. it doesn't do anythingthat can't be done on the command line its just more obvious in my books.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by healdem
    in my books theres a lot to be said for using the tools provided by the same company as the DB you are using. I'd agree if you expect to use Oracle, the use the Oracle tool.
    My project work requires me to work with a lot of different DBMS and thus I basically do the opposite:
    I try to use a single tool for all of them, if at all possible. I admit that administration tasks will never be supported by a "all-in-one" the same way as with a specialized tool. But then admistration is not part of my usual work (only running SQL stuff), so this is not that important for me.
    I don't know the oracle tool, but the MySQL query browser in my books has a lot of neat features which integrate well with the server. not least is the help and SQL reference which is a boon to people just starting out on the SQL trial.
    SQL Developer will definitely never match with any MySQL specific tool when it comes to administration, absolutely.
    I would also switch to a specialized tool immediately, when I find my tool makes things harder to do

Posting Permissions

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