Mr. Puneet Kishor wrote: > On Jun 3, 2011, at 6:16 PM, Darren Duncan wrote: >> a. MySQL silently ignores all CHECK constraints in all engines, so for >> example you can't even tell it you want a column to only hold values >> between 1 and 10. Its in the MySQL docs: "The CHECK clause is parsed but >> ignored by all storage engines." > > Yes and no. Apparently the above was true before 5.0.2, but apparently it has > been fixed since then. From the docs,
No, it hasn't been fixed at all. Check constraints are accepted and silently ignored, and even with strictness enabled, you still don't even get a warning, never mind a rejection. From http://dev.mysql.com/doc/refman/5.5/en/create-table.html (the MySQL 5.5 manual): "The CHECK clause is parsed but ignored by all storage engines.". Whenever I listed a MySQL deficiency in this thread, that I encountered, I made sure to check whether the latest MySQL version still had the problem, and I only reported deficiencies that are not addressed in the latest available version, so 5.5.x in this case. Also, I'm using version 5.0.30 specifically, which is newer than 5.0.2. Observe ... ------- mysql> create table test2 (myposint integer, check (myposint > 0)); Query OK, 0 rows affected (0.00 sec) mysql> SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 'STRICT_ALL_TABLES';Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> insert into test2 set myposint = -1; Query OK, 1 row affected (0.00 sec) mysql> show warnings; Empty set (0.00 sec) mysql> select * from test2; +----------+ | myposint | +----------+ | -1 | +----------+ 1 row in set (0.00 sec) mysql> desc test2; +----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------+------+-----+---------+-------+ | myposint | int(11) | YES | | NULL | | +----------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) ------- > "Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data values > and coerces them to legal values for data entry. In MySQL 5.0.2 and up, that > remains the default behavior, but you can change the server SQL mode to > select more traditional treatment of bad values such that the server rejects > them and aborts the statement in which they occur." > > and > > "In MySQL 5.0.2 and up, you can select stricter treatment of input values by > using the STRICT_TRANS_TABLES or STRICT_ALL_TABLES SQL modes: > > SET sql_mode = 'STRICT_TRANS_TABLES'; SET sql_mode = 'STRICT_ALL_TABLES'; That is talking about inserting values not of the declared base types of the columns, and not about CHECK constraints. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
