I think you are mistaken, at least historically. MySQL indeed has a history of allowing you to insert NULL values into NOT NULL columns, but instead of inserting nulls it silently converts the NULL you specified into an empty string. This is really bad.
There are a number of places in the MySQL manual that address this: http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html If strict SQL mode is not enabled, MySQL sets the column to the implicit default value for the column data type. If strict mode is enabled, an error occurs for transactional tables and the statement is rolled back. For nontransactional tables, an error occurs, but if this happens for the second or subsequent row of a multiple-row statement, the preceding rows will have been inserted. http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html If you are not using strict mode (that is, neither STRICT_TRANS_TABLES nor STRICT_ALL_TABLES is enabled), MySQL inserts adjusted values for invalid or missing values and produces warnings. In strict mode, you can produce this behavior by using INSERT IGNORE or UPDATE IGNORE. See Section 12.4.5.37, “SHOW WARNINGS Syntax”. So, it works as expected if you're in strict mode, which I believe was only added in version 5.x but please don't quote me on that. Also, it was off by default for a really long time (and may still be). On Thu, Apr 14, 2011 at 10:19 AM, Matija Grabnar <[email protected]> wrote: >> If you care about keeping your data consistent, do not use it. You can >> never rely on MySQL honoring constraints, so you have to do all your data >> integrity checking in the application. As a trivial demonstration, create a >> table with a NOT NULL Text column and then insert an empty row. MySQL will >> happily do as you say, putting an empty string in the Text column. It is >> possible to bludgeon MySQL into nearly being reliable, but it's not the >> default behavior and it will vanish at the slightest mistake. > > I can see how people can like Postgresql more than MySQL - but this > statement is really going a bit too far. > > Yes, you can put an empty string into a NOT NULL text column. You can also > put a zero > into a NOT NULL int column. Both are legitimate values for the type. And no, > mysql will NOT > accept a NULL value in a NOT NULL text column. > > mysql> create table foo (a text not null); > Query OK, 0 rows affected (0.05 sec) > mysql> insert into foo values (NULL); > ERROR 1048 (23000): Column 'a' cannot be null > mysql> insert into foo values (''); > Query OK, 1 row affected (0.00 sec) > > There may be areas where Postgresql is better than MySQL, but at various > times I've found > MySQL to be vastly faster than Postgresql for the same data: Because I knew > I would be handling a LOT of data, I benchmarked both with a real-life > sample of my data and a real-life mix of operations on them. I suggest that, > if you're serious about picking the right database, you do the same. > > > > _______________________________________________ > List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class > IRC: irc.perl.org#dbix-class > SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ > Searchable Archive: > http://www.grokbase.com/group/[email protected] > _______________________________________________ List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/[email protected]
