Wednesday, April 21, 2021

Default Values & Check Constraint and Midwest PHP

 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.

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.