Add The Not Null Constraint To A Column

I'm using PHPMyAdmin and I try to add the NOT NULL constraint to a column of my table. PHPMyAdmin accepts my following query : ALTER TABLE `wall` MODIFY `token_message` varchar(40)

Solution 1:

You wrote, "I can still insert empty strings (=NULL)," which sounds like a misunderstanding. In SQL, an empty string does not evaluate to NULL, or vice versa. Try inserting an empty string and doing SELECT from wall where token_message is NULL. You should get zero rows back. Then try doing an insert where you specify NULL (unquoted) as the value for your column, and you should get the expected error message.

If those tests work as expected, then everything is fine, and your problem is actually that you want to prevent blank strings from being inserted. Check out this question for suggestions, or just check for blank strings during validation, before the query.

Solution 2:

MySQL's column alter syntax requires you to completely re-specify the column. You can't just change one attribute of a column, you have to re-define it completely:

ALTERTABLE wall MODIFY token_message varchar(40) NOTNULLdefault''

The only 'SET' version allowed is to change the default value.

ref: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Solution 3:

I think this is a matter of scrubbing your inputs. As octern mentioned, an empty string ('') is not a NULL value in sql. The best way to handle this is to only allow updates through a store procedure which strips out empty strings, even space characters:

CREATE PROC InsertIntoMyDb (@MyVarCharVARCHAR(2000)) ASSET@MyVarChar=NULLIF(RTRIM(LTRIM(@MyVarChar)), '')

INSERTINTO [TBL] (MyVarChar)
VALUES@MyVarChar

This will truncate any number of spaces to an empty string, turn an empty string into a NULL, and then it will not allow the NULL value to be inserted based on the constraint you already have in place.

Solution 4:

Try to use this query

Alter table table_name 
change column_name column_name datatype(length) definition

ie,

Altertable wall 
change tocken_message tocken_message varchar(40) NOTNULLDEFAULT