Add the NOT NULL constraint to a column

Question

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;

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, Apr 18 '12 at 21:27
have you tried to create a new column and copy the data over? — Hajo, Apr 18 '12 at 21:28
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, Apr 18 '12 at 21:31
@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, Apr 18 '12 at 21:31

score 6 · Accepted Answer · edited May 23 '17 at 11:51

6

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.

edited May 23 '17 at 11:51

Community

1
1

answered Apr 18 '12 at 21:34

octern

4,825
21
38

1

The correct test is `WHERE token_message IS NULL`. You can't compare Nulls with `=`. – ypercubeᵀᴹ Apr 18 '12 at 21:44
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 Apr 18 '12 at 22:10

score 3 · Answer 2 · answered Apr 18 '12 at 21:42

3

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

answered Apr 18 '12 at 21:42

Marc B

356,200
43
426
500

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 Apr 18 '12 at 22:12

score 0 · Answer 3 · answered Apr 18 '12 at 22:09

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.

The question is tagged with MySQL, not SQL-Server. – ypercubeᵀᴹ Apr 18 '12 at 22:13 — ypercubeᵀᴹ, Apr 18 '12 at 22:13

score 0 · Answer 4 · edited Aug 24 '14 at 20:08

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

edited Aug 24 '14 at 20:08

Brad Larson

170,088
45
397
571

answered Jan 20 '14 at 04:40

php

4,307
1
24
13

Add the NOT NULL constraint to a column

4 Answers4

Related