Page 1 of 1
mysql data integrity
Posted: Mon Jun 10, 2002 7:26 am
by Rowan Williams
I am having problems with data integrity in MySQL - specifically at field level. I need to find a way to remove the automatic default value that mysql creates for 'not null' fields. EG) Mysql creates a default of 0 for any 'not null', integer field. This defeats the purpose of having the 'not null' constraint because any null values that are entered into the field turn into 0 - rather than return return a data error, which is what i require. Does anyone know of a way around this? Is it possible to create any sort of validation rules in MySQL?
Posted: Mon Jun 10, 2002 8:54 am
by Wayne
Make your SQL command in your php script :
Code: Select all
INSERT INTO table VALUES(NULLIF("$var1",""),NULLIF("$var2",""))
If your variables $var1 or $var2 are empty/unset this will make the value NULL and MySQL will not accept it in a NOT NULL column, and will not insert the data.
Posted: Mon Jun 10, 2002 9:22 am
by Rowan Williams
I failed to mention that this problem is most apparent when transferring data through text files. I am moving large amounts of data from one mysql db to another and am having problems catching corrupt data, and import errors, as mysql very rarely returns errors from LOAD statements.
Posted: Mon Jun 10, 2002 9:32 am
by Wayne
what format is the file in? how are you generating it, from a sql dump or a select statement outputted to a file?
Posted: Mon Jun 10, 2002 9:32 am
by jason
Actually, if you want to enter NULL data into MySQL, don't say NOT NULL, which will not allow the data to be NULL. So that means if you are entering NULL data, and you told MySQL the field is NOT NULL, it has to enter something, and unless you define the DEFAULT to be something else, it will enter 0.