MySQL Forums
Forum List  »  InnoDB

Check constraint not working
Posted by: Anish Philip
Date: October 27, 2012 05:28AM

Hi guys

This is my first post in this forum. I am not sure if this is right place to post this question. I have a question regarding my check constraint. I created a table "item" with a CHECK constraint on columns ROL and QOH to check if ROL<QOH. It seems that the constraint is not working as the table is accepting values for QOH less than ROL! Please tell me if there is any syntax error in the below commands!

mysql> CREATE TABLE items (
-> icode char(5) NOT NULL,
-> descp char(20) NOT NULL,
-> ROL integer,
-> QOH integer,
-> PRIMARY KEY (icode),
-> CHECK (ROL<QOH));
Query OK, 0 rows affected (0.10 sec)

mysql> DESCRIBE items;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| icode | char(5) | NO | PRI | NULL | |
| descp | char(20) | NO | | NULL | |
| ROL | int(11) | YES | | NULL | |
| QOH | int(11) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> INSERT INTO items (icode, descp, ROL, QOH) VALUES ('ABC', 'New employee', 234, 500);
Query OK, 1 row affected (0.36 sec)

mysql> SELECT * FROM items;
+-------+--------------+------+------+
| icode | descp | ROL | QOH |
+-------+--------------+------+------+
| ABC | New employee | 234 | 500 |
+-------+--------------+------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO items (icode, descp, ROL, QOH) VALUES ('DEF', 'New employee', 224, 100);
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM items;
+-------+--------------+------+------+
| icode | descp | ROL | QOH |
+-------+--------------+------+------+
| ABC | New employee | 234 | 500 |
| DEF | New employee | 224 | 100 |
+-------+--------------+------+------+
2 rows in set (0.00 sec)

mysql>

____________________________________________________________________________________________________________________________________________
OS:Fedora 16
RDBMS:MySQL
Workspace:GNOME Terminal

Options: ReplyQuote


Subject
Views
Written By
Posted
Check constraint not working
9626
October 27, 2012 05:28AM
2430
October 28, 2012 03:06PM
1887
October 28, 2012 03:10PM
1956
October 31, 2012 03:08AM
1626
October 31, 2012 07:22AM
2304
October 31, 2012 09:00AM
1758
November 08, 2012 01:03PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.