mos wrote:
At 04:00 PM 10/28/2003, you wrote:
That's not how it works. The following demonstrates the feature I think you want.
>mysql test test> create table testnull ( X varchar(10) not null ); Query OK, 0 rows affected (0.11 sec)
test> insert into testnull values ( null ); ERROR 1048: Column 'X' cannot be null test>
Kevin,
Yes, that's what I thought too for the longest time. But it doesn't work that way when using Load Data Infile. This command will actually convert NULL's to an empty string or zero for the NOT NULL fields. I now get invalid data in my tables. Nasty. :-(
This behavior is not specific to Load Data Infile, it is general to mysql. You might want to take a look at the docs at <http://www.mysql.com/doc/en/constraint_NOT_NULL.html>. The first line is, "To be able to support easy handling of non-transactional tables, all fields in MySQL have default values." Mysql automatically converts missing or illegal values to column defaults, because in the non-transactional case, it cannot roll back when you have an illegal value in the the 300th row of a 500 row insert.
Kevin's example is actually a special case. Consider:
mysql> create table testnull (X varchar(10) not null); Query OK, 0 rows affected (0.51 sec)
mysql> insert into testnull values (null); ERROR 1048: Column 'X' cannot be null
mysql> insert into testnull values ('first'), (null), ('third');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 1mysql> select * from testnull; +-------+ | X | +-------+ | first | | | | third | +-------+ 3 rows in set (0.01 sec)
As you can see, you only get an error in the single row insert case. For multiple rows, the illegal NULL is replaced with the column default ('' in this case), as you see using LOAD DATA INFILE.
The short term solution is to validate the data in the input file before it gets imported. I was hoping there was an option in Load Data Infile to prevent it from converting NULL's to '' or 0. I'd rather have invalid data rejected than converted.
Mike
The usual solution is the one you mention: Verify your data before inserting or loading, rather than counting on mysql to reject bad data. Another option might be to initially define the columns to allow NULL, as there are NULLs in the data. You could then find the bad data by searching for the NULLs:
select * from my_table where should_not_be_null_col IS NULL;
Then do whatever is appropriate. Of course, which way is easier will depend on your situation.
There is an alternative solution, but it may be overkill compared to what you want. You can build a copy of mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option. I have never tried this, but as I understand it, this will turn off defaults completely, yielding errors for missing or illegal values. Note that I believe you cannot even explicitly set a default for a column if you use this option, but I could be wrong.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
