MYSQL have set a column to not null but still it's accepting null value

Question

Structure for a table in mysql5.5

tablename:

id:
name:
detail:

here even altering the table:

ALTER TABLE tablename MODIFY name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL;

OR

ALTER TABLE `tablename` CHANGE `name` `name` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL DEFAULT NOT NULL;

after inserting value:

INSERT INTO `databasename`.`tablename` (`id`, `name`, `detail`) VALUES (NULL, '', 'asdfasfdadsfadsfafd');

query has run successfully no error row has been added to table,

how can i prevent such empty or null.

regards

Empty string and `null` values are different, `NOT NULL` constraint will not prevent empty strings. For empty strings you can use `check` constraints like in this question: http://stackoverflow.com/questions/2514178/im-looking-for-a-constraint-to-prevent-the-insert-of-an-empty-string-in-mysql — Mahmoud Gamal, Nov 16 '14 at 10:41

score 0 · Answer 1 · 2014-11-16T11:30:44.720

0

Try this:

ALTER TABLE tablename 
MODIFY COLUMN name VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL CHECK (name <> '');

DEMO:

mysql> create table tablename(id int(2) not null, something varchar(25) null, primary key(id));
Query OK, 0 rows affected (0.15 sec)
mysql> insert into tablename values(0,'hello');
Query OK, 1 row affected (0.38 sec)

mysql> insert into tablename values(1,'salut');
Query OK, 1 row affected (0.31 sec)

mysql> select * from tablename;
+----+-----------+
| id | something |
+----+-----------+
|  0 | hello     |
|  1 | salut     |
+----+-----------+
2 rows in set (0.00 sec)

Now, I run the UPDATE command:

mysql> ALTER TABLE tablename  MODIFY COLUMN something VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_persian_ci NOT NULL;
Query OK, 2 rows affected (0.70 sec)
Records: 2  Duplicates: 0  Warnings: 0

I insert a normal row:

mysql> insert into tablename values(2,'france');
Query OK, 1 row affected (0.29 sec)

But it does not allow me to insert a NULL value:

mysql> insert into tablename values(3,NULL);
ERROR 1048 (23000): Column 'something' cannot be null

I check to be sure:

mysql> select * from tablename;
+----+-----------+
| id | something |
+----+-----------+
|  0 | hello     |
|  1 | salut     |
|  2 | france    |
+----+-----------+
3 rows in set (0.00 sec)

edited Nov 16 '14 at 11:30

answered Nov 16 '14 at 10:17

No effect Same result. – SAR Nov 16 '14 at 10:39
@abas_rafiq I tried the command I gave you on a test table I created. You can see it does its job very well (it can not allow me NULL values insertion). But in your case, I do not understand why. I suggest you to show your MySQL version – Nov 16 '14 at 10:52
please try (insert into tablename values(3,'');) – SAR Nov 16 '14 at 11:10
@abas_rafiq NO, you are wrong: `''` is different from `NULL` because `''` means empty string so it will accept it of course – Nov 16 '14 at 11:15
yes on my insert i have explain not null i mean not empty. – SAR Nov 16 '14 at 11:26
@abas_rafiq in MySQL NOT NULL is not the same as not empty (`''` different from `NULL`) . To do the both things, please check my update – Nov 16 '14 at 11:30

MYSQL have set a column to not null but still it's accepting null value

1 Answers1