Check Constraints and Duplicate Names

i was working on some example code for using check constraints and was fairly happy with my first bit of of code.

CREATE TABLE c1 (x INT 
                   CONSTRAINT x_gt_0 CHECK (x > 0)
);

it worked well and did what I wanted which was to reject data where the value for x was not one or greater. MySQL has allowed you to have constraint checks for many years but they only came to life in 8.0.16.  Before that version the checks were simply ignored.

So I was writing some more demo code and smacked into what I thought was an odd error.

CREATE TABLE c2 (x INT 
                   CONSTRAINT x_gt_0 CHECK (x > 0), 
                   CONSTRAINT x_lt_10 CHECK ( x < 10)
);
ERROR: 3822: Duplicate check constraint name 'x_gt_0'.

I did have a constraint in another table named x_gt_0.  It seemed odd to me and it was odd enough that it sent me to the manual.  If you refer to 13.1.20.7 CHECK Constraints of the MySQL 8.0 server manual, there in black and white is

The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema (database). Consequently, CHECK constraint names must be unique per schema; no two tables in the same schema can share a CHECK constraint name. (Exception: A TEMPORARY table hides a non-TEMPORARY table of the same name, so it can have the same CHECK constraint names as well.)

Ah, it is because of the danged named spaces.  And if the SQL standard specifies then I have to live with it.

Work around? Yes, I quickly renamed the constraint in question getting what I wanted.  In this case it was not a big thing as the name of the constraint, while descriptive, was not named by some imperative.

>CREATE TABLE c2 (x INT 
                  CONSTRAINT x_gt_1 CHECK (x > 1), 
                  CONSTRAINT x_lt_10 CHECK ( x < 10)
);
Query OK, 0 rows affected (0.0801 sec)
 >insert into c2 (x) values (100);
ERROR: 3819: Check constraint 'x_lt_10' is violated.
>insert into c2 (x) values (0);
ERROR: 3819: Check constraint 'x_gt_1' is violated.
>insert into c2 (x) values (4);
Query OK, 1 row affected (0.0035 sec)

So there is always another way to skin a cat but how many skinless cats do you need hanging around?         

If you want to be proactive you can refer to the INFORMATION_SCHEMA.check_constraints table to see if you have already used that name.

>select * from INFORMATION_SCHEMA.check_constraints 
        where CONSTRAINT_NAME='x_gt_0'\G

*************************** 1. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: demo
   CONSTRAINT_NAME: x_gt_0
      CHECK_CLAUSE: (`x` > 0)
*************************** 2. row ***************************
CONSTRAINT_CATALOG: def
 CONSTRAINT_SCHEMA: xtest
   CONSTRAINT_NAME: x_gt_0
      CHECK_CLAUSE: (`x` > 0)
2 rows in set (0.0017 sec)

Remember you can reuse the name but it has to be in a different schema. That is why I can have x_gt_0 used twice but once in the demo schema and once in the xtest schema.







create table porridge (id int unsigned auto_increment,
temp integer
       constraint too_hot check (temp > 100),
       constraint too_cold check ( temp < 70),
primary key(id)
);