Consider the following table definition:
CREATE TABLE check_please (
c1 int constraint c1_not_42 check (c1 <> 42) default 42);
CHECK Constraints are a few years old in MySQL and are very useful. You set up a constraint and the MySQL server ensures that the data being inserted does not violate that constraint.
But what if the DEFAULT value violates that constraint? Which takes precedence - the default value of the constraint? We start with some direct action:
insert into check_please (c1) value(42);
ERROR: 3819: Check constraint 'c1_not_42' is violated.
ERROR: 3819: Check constraint 'c1_not_42' is violated.
That worked as expected. There is no easy way to cram a value of 42 into column c1.
That was a direct test. But what if we try something different? A second column is added so we can test not supplying a value to column c1.
CREATE TABLE check_please2 (
c1 int constraint c1x_not_42 check (c1 <> 42) default 42,
c2 int default 42);
And the test supplying no value for c1 which should try to insert the default value of 42, which violates the constraint check.
insert into check_please2 (c2) values (NULL);
ERROR: 3819: Check constraint 'c1x_not_42' is violated.
It does seem pretty obvious that a default value would not override a constraint but sometimes you just have to check your assumptions.
Midwest PHP
Midwest PHP is an online conference April 22nd and 23rd with 52 Sessions, Certifications, & More! And there are free tickets! MySQL is again proud to sponsor this event and I speak on the first day on new MySQL 8.0 features.
