MySQL Varchar Primary Key With Not Null Constraint Accepting Nulls

Question

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 ('[email protected]');

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?

Answer 1

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 ('[email protected]');
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.