Menu
In this question I learned how to prevent the insert of a NULL value. But, unfortunately, an empty string is being inserted anyway. Apart from preventing this on the PHP side, I'd like to use something like a database constraint to prevent this. Of course a check on the application side is necessary, but I'd like it to be on both sides.
I am taught that whatever application is talking to your database, it should not be able to insert basically wrong data in it. So...
CREATE TABLE IF NOT EXISTS tblFoo ( foo_id int(11) NOT NULL AUTO_INCREMENT, foo_test varchar(50) NOT NULL, PRIMARY KEY (foo_id) ); Would still allow me to do this insert:
INSERT INTO tblFoo (foo_test) VALUES (''); Which I would like to prevent.
822
SELECT column_names FROM table_name WHERE column_name IS NOT NULL; Query: SELECT * FROM Student WHERE Name IS NOT NULL AND Department IS NOT NULL AND Roll_No IS NOT NULL; To exclude the null values from all the columns we used AND operator.
In MySQL, a NULL value means unknown. A NULL value is different from zero ( 0 ) or an empty string '' . A NULL value is not equal to anything, even itself. If you compare a NULL value with another NULL value or any other value, the result is NULL because the value of each NULL value is unknown.
To avoid a NULL value in a table column in MySQL we specify NOT NULL constraint when you create a table. But NOT NULL constraint still allows empty values. To avoid empty values while inserting a record we can have a check in the table DDL itself.
In the above syntax, if you compare empty string( ' ') to empty string( ' '), the result will always be NULL.
Normally you would do that with CHECK constraint:
foo_test VARCHAR(50) NOT NULL CHECK (foo_test <> '') Prior to Version 8.0 MySQL had limited support for constraints. From MySQL Reference Manual:
The CHECK clause is parsed but ignored by all storage engines.
If you must stick to an old version use triggers as a workaround, as people have pointed out.
In future, you may want to take a look at PostgreSQL, which is considered to have better support for data integrity (among other things) by many people.
130
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
