Q) Integrity Constraint using CHECK in a CREATE query.

[Bruce Oh]

Hi all,

I've tried with an Integrity Constraint using CHECK in a CREATE query.

mySql honored the query, but I didn't see any constraint checking by mySQL 5.1.

Please see the query sequence below.

Thank you.




====below====

mysql> CREATE TABLE Account3 (
-> CreditCardNo VARCHAR(16) PRIMARY KEY,
-> ExpirationDate VARCHAR(6) NOT NULL,
-> Status CHAR(1) NOT NULL,
-> CHECK (
-> NOT EXISTS (
-> SELECT *
-> FROM Account3
-> WHERE ExpirationDate='092011'
-> )
-> )
-> );
Query OK, 0 rows affected (0.13 sec)

mysql>
mysql> INSERT INTO Account3 VALUES ('5444444444444444', '082010', 'A');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Account3 VALUES ('4555555555555555', '092010', 'A');
Query OK, 1 row affected (0.03 sec)

mysql> INSERT INTO Account3 VALUES ('4666666666666666', '092011', 'A');
Query OK, 1 row affected (0.16 sec)

mysql> INSERT INTO Account3 VALUES ('4777777777777777', '092011', 'A');
Query OK, 1 row affected (0.05 sec)

mysql> SELECT *
-> FROM Account3
-> WHERE ExpirationDate='092011';
+------------------+----------------+--------+
| CreditCardNo | ExpirationDate | Status |
+------------------+----------------+--------+
| 4666666666666666 | 092011 | A |
| 4777777777777777 | 092011 | A | <=========
+------------------+----------------+--------+
2 rows in set (0.00 sec)