| To be able to support easy handling of non-transactional tables, all | columns in MySQL have default values. | | If you insert an ``incorrect'' value into a column, such as a NULL into a | NOT NULL column or a too-large numerical value into a numerical column, | MySQL sets the column to the ``best possible value'' instead of producing | an error:
and concludes:
| This means that you should generally not use MySQL to check column | content. Instead, the application should ensure that it passes only legal | values to MySQL...
David Griffiths wrote:
It's best practice to write unit tests for all your code, with calculated data to show what you expect. That data then gets compared to what is actually generated and if there is a discrepency, then you have a bug somewhere.
But the reason modern databases have foreign keys, primary keys, not-nulls, check constraints and data-metadata (char(5), INT, BIGINT, etc) is to prevent bad data from going in.. If no exception is thrown
MyISAM tables do not have foreign key and check constraints. I'm not sure what you mean by data-metadata.
because you are trying to put a BIGINT into an INT, then why throw one if you try to insert a NULL into a NOT-NULL column (assuming no DEFAULT is present)? Or what about foreign keys? Why not just quietly fail if a fk-constraint is violated?
MySQL will only throw an exception if you try to explicitly insert a NULL into a NOT-NULL column in a single row insert. It won't throw an exception in a multi-row insert or if you implicitly set a column to NULL by leaving it out. This is in keeping with the need to support non-transactional tables.
Foreign keys are different. They are only supported in transactional tables (InnoDB), so ROLLBACK is available.
Go even farther. Say your SQL is just incorrect ("INSETR IN TO " instead of "INSERT INTO"). What if MySQL didn't throw an exception back to your PERL DBI or Java JDBC connection? After all, it's up to the developer to make sure their SQL syntax is correct.
Sure, MySQL could do that, but then no one would use it. In any case, that hypothetical has no bearing on the discussion at hand. The issue is what MySQL does, not what ridiculous thing it could do.
There is a reason the MySQL developers do things the way they do. It's documented in the manual. The driving principle is the need to support non-transactional tables, not some idea that no data validity checking should ever be done by the db.
The database has all sorts of constraints that can be applied to your data model. They should all have the same behaviour when violated.
The developers have explained why this is not so for MySQL.
This is a philosophical matter. There have been excellent arguments for both sides in previous threads on this topic. As it stands now, however, if you require a db where the db itself can be set up to validate all data, then MySQL is not the db for you. On the other hand, if you are willing to validate your data, MySQL is fast, reliable, and cheap. Personally, I don't find coding to validate input any more difficult than coding to handle exceptions.
David
Michael
Mike Johnson wrote:
From: David Griffiths [mailto:[EMAIL PROTECTED]
MySQL really should throw an exception/error rather than just quietly trim your data and accept it. When your data is critical, and your business depends on it, you can't have bad data quietly going into the database.
Someone correct me if I'm wrong, but isn't it considered best practice to validate data before it gets to the database?
I can't seem to find a source for this after a quick search on Google, though...
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
