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 ('firstname.lastname@example.org');
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?