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. :-(
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
> -----Original Message----- > From: mos [mailto:[EMAIL PROTECTED] > Sent: Tuesday, October 28, 2003 1:50 PM > To: [EMAIL PROTECTED] > Subject: How to define a required field in a table? > > > Is it possible to define required fields in a MySQL table? > By that I mean if the row is updated and the field value is > still null, the > update for the row is rejected and an error is produced. > > The "NOT NULL" clause in the table definition simply converts > NULL values > to '' or zero for string and numeric fields. This only makes > things worse > because now it is harder to spot invalid data because the > zeroes and '' > blends in with valid data. > > So I need the update to fail so I can keep invalid data > (NULL's) out of the > table. Any idea on how to do this? TIA > > Mike > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > >
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
