mysql data integrity

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Rowan Williams
Forum Newbie
Posts: 2
Joined: Mon Jun 10, 2002 7:26 am

mysql data integrity

Post 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?
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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.
Rowan Williams
Forum Newbie
Posts: 2
Joined: Mon Jun 10, 2002 7:26 am

Post 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.
User avatar
Wayne
Forum Contributor
Posts: 339
Joined: Wed Jun 05, 2002 10:59 am

Post 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?
jason
Site Admin
Posts: 1767
Joined: Thu Apr 18, 2002 3:14 pm
Location: Montreal, CA
Contact:

Post 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.
Post Reply