MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls

MySQLnullprimary-key

I have the following table definition:

CREATE TABLE `invite` (
  `id` varchar(255) CHARACTER SET utf8 NOT NULL,
  `email` varchar(255) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uc_i_e` (`email`)
)

My goal with the not null constraint on id is to make sure there are no null values as the primary key, and to ultimately fail to insert.

However, not specifying the id on insert works just fine:

INSERT INTO invite (`email`) VALUES ('e@mail.com');

There is an 1364 warning specifying Field 'id' doesn't have a default value, but the insert succeeds just fine.

It looks like MySQL is using the not null constraint to turn this into a non null (blank) value at insert.

If I actually specify null as a value of id, the insert fails as expected.

Without having a trigger to stop empty strings being inserted, how can I stop this scenario from happening, i.e force a non empty string value to be specified?

Best Answer

To prevent this insert from occurring, turn on strict mode.

mysql> set session sql_mode = 'STRICT_TRANS_TABLES';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> INSERT INTO invite (`email`) VALUES ('e@mail.com');
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> select * from invite;
Empty set (0.00 sec)

Note: Be sure to follow the description in the link I provided, NOT copy my command regarding setting sql_mode. Otherwise you may disable other settings.

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql Not Null Columns accepting null values

It actually doesn't accept NULL values it considers it as empty string. That's because you have your server in non-strict mode. That controls how MySQL handles invalid or missing values in inserts and updates. You can read more about modes here: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

mysql> insert into cities(state_id) values (20);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+

mysql> select name is null from cities where id = LAST_INSERT_ID();
+--------------+
| name is null |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into cities(state_id) values (20);
ERROR 1364 (HY000): Field 'name' doesn't have a default value

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql Not Null Columns accepting null values

Related Question