0

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?

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 ('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.

  • Hi thanks for this. I'll probably not turn this setting on then and just get used to the quirk (as it seems to me). – Clarkey Sep 21 '18 at 8:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.