define constraints in mysql?! - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

define constraints in mysql?!

KyonKyon Posts: 113Member
Hi.

I've got some fairly simple problem but can't get it to work. mySQL allows to define constraints on table rows with a check condition. What I've been trying some time now is the following simple SQL-statement:
[code]
CREATE TABLE numbers (no INT NOT NULL CONSTRAINT con_pos CHECK no >= 0);
[/code]
When I try to execute this I get an syntax error message. Maybe it's some really stupid question, but could please somebody clear this up for me.

Cheers & thx,
Kyon

-- planning is the substitution of hazard by error --

Comments

  • Ara_camAra_cam Posts: 37Member
    From looking at the manual, it appears that the Constraint and the Check variables do not go together, so try this syntax:
    [code]
    CREATE TABLE numbers (no INT NOT NULL CHECK (no >= 0));
    [/code]
    The Constraint keyword is used for foreign key references.

    Hope this helps!


    : [code]
    : CREATE TABLE numbers (no INT NOT NULL CONSTRAINT con_pos CHECK no >= 0);
    : [/code]
    : When I try to execute this I get an syntax error message. Maybe it's some really stupid question, but could please somebody clear this up for me.

  • KyonKyon Posts: 113Member
    Hi.

    Already tried that but w/o success - thanks anyway.

    Cheers,
    Kyon

    -- planning is the substitution of hazard by error --

  • Ara_camAra_cam Posts: 37Member
    I did some further testing, and as of the latest version, the Check option doesn't actually do anything. It is ignored by MySQL.

    (Here is where I've created your table, and added the constraint to the database, then tested it):
    [code]
    mysql> create table numbers (no int not null);
    Query OK, 0 rows affected (0.00 sec)

    mysql> alter table numbers
    -> add constraint con_pos
    -> check (no >=0 );
    Query OK, 0 rows affected (0.03 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> insert into numbers (no) values (-1);
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from numbers;
    +----+
    | no |
    +----+
    | -1 |
    +----+
    1 row in set (0.02 sec)
    [/code]

    From the MySQL online user manual http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#CREATE_TABLE :
    [code]
    The FOREIGN KEY, CHECK, and REFERENCES
    clauses don't actually do anything [/code]

    So, I guess to make the Check constraint work, you will have to use program logic, rather than database logic. Unfortunately, MySQL tries to remove as much integrity coding as possible. For instance, the Foreign Key options don't really do anything either, unlike most databases. They expect the program acting as a front-end to the database to handle all the constraints.

    I know this isn't the answer you were looking for, but at least you know where to go next.
  • KyonKyon Posts: 113Member
    Hi.

    Thanks for the effort you made for checking this out. So I'll put all that checking in my sourcecode. It's just a little bit astonishing that such a simple thing as a check constraint has not been realized yet in MySQL. Especially since I've been working before with Oracle.

    But now I know what to do.

    Thanks again

    Cheers,
    Kyon

    -- planning is the substitution of hazard by error --

Sign In or Register to comment.