Add the NOT NULL constraint to a column

3

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) NOT NULL;

But I can still insert empty strings (=NULL), I don't understand why.

PS : If you're going to give me some other queries to add this constraint, note I've have tried these 3 which don't work in my PHPMyAdmin (kind of error : #1064 - You have an error in your SQL syntax; check the manual) :

ALTER TABLE `wall` ALTER COLUMN `token_message` SET NOT NULL;
ALTER TABLE `wall` ALTER COLUMN `token_message` varchar(40) NOT NULL;
ALTER TABLE `wall` MODIFY `token_message` CONSTRAINTS token_message_not_null NOT NULL;
Anon

출처 mysql sql phpmyadmin
  • Define "don't work in my PHPMyAdmin." Perhaps you already have null values in that column, in which case the DB won't let you add that constraint (at least not without making those null values something other than null first). - Matt Ball
  • have you tried to create a new column and copy the data over? - Hajo
  • I have already verified, I haven't already the null value in that column. The error I have when I try the 3 other queries is : #1064 - You have an error in your SQL syntax - Anon
  • @MДΓΓБДLL I tested this, and when I changed a varchar column with NULL values to NOT NULL, it added the constraint with no problem. The NULL values were automatically changed to blank strings (with a warning about "truncating" the data). - octern

4 답변

5

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.

  • The correct test is WHERE token_message IS NULL. You can't compare Nulls with =. - yper-crazyhat-cubeᵀᴹ
  • Thanks. I was assuming that = would give a less rigorous comparison that accounted for any unexpected type conversion, but that's totally not how it works in SQL (which I should know by now, considering how important the whole ternary logic thing is). I've edited my answer. - octern
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:

ALTER TABLE wall MODIFY token_message varchar(40) NOT NULL default ''

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

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

  • This is true, and relevant to the alternate queries the OP tried after the first one didn't work. But it can't explain their original problem, since the first query they tried was totally legitimate and accepted (it didn't have the default value, but that's optional anyway). - octern
0

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 (@MyVarChar VARCHAR(2000)) AS

SET @MyVarChar = NULLIF(RTRIM(LTRIM(@MyVarChar)), '')

INSERT INTO [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.

0

Try to use this query

Alter table table_name 
change column_name column_name datatype(length) definition

ie,

Alter table wall 
change tocken_message tocken_message varchar(40) NOT NULL DEFAULT

Linked

Related

Latest