MySQL data integrity?

Question

In various PostgreSQL vs. MySQL comparisons I've seen many mentions of problems with data integrity in MySQL. Is this currently still an issue?

Particularly for web applications using MySQL, are there tricks to making sure data integrity is maintained? Or with new versions is this true 'out of the box' with no additional configuration required?

Answer 1

MySQL has a feature to support multiple storage backends (storage engine). The most prominent ones are MyISAM and InnoDB. MyISAM is the default, in many situations quite fast but provides no data integrity. InnoDB supports full data integrity. When creating a table you can set the type.

CREATE TABLE foo (...) ENGINE=innodb;

See also http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html for more details.

Answer 2

By default MySQL created myisam engine tables, which does not support transactions or foreign keys, you need to explicitly force transactional innodb engine while creating a table.

By default MySQL accepts invalid data, like date '2010-02-30', silently truncates too long textual data, too big numbers etc. But you can change it for INNODB tables using SET sql_mode = 'STRICT_TRANS_TABLES'; on mysql 5.0.2 and up - see "Constraints on Invalid Data".

MySQL does not support check constraints at all, so you can not for example:

• force integer data to be at some range;
• force text data to be at some format (for example valid e-mail address or valid url);
• limit text data to valid characters (only ASCII, only ISO-8859-1, only numbers and minus etc.);
• disallow spaces, newlines, double-spaces, spaces at the end etc. or empty text data.

So all data validation has to be done in client application. Which is harder to do and more error-prone.

All of this is not a problem in PostgreSQL.

Answer 3

No storage engine for MySQL supports check constraints. Nuff said.

"For other storage engines, the clauses are parsed but ignored. The CHECK clause is parsed but ignored by all storage engines." "The reason for accepting but ignoring syntax clauses is for compatibility, to make it easier to port code from other SQL servers, and to run applications that create tables with references" - http://dev.mysql.com/doc/refman/5.1/en/alter-table.html No enhancements for this in MySQL 5.4 or 5.5 according to the manual.