Constraint check name uses wrong error code

[dveeden]

Bug Report

In MySQL error 3822 is used for duplicate check constraint names, but TiDB uses 1061.

1. Minimal reproduce step (Required)

CREATE TABLE t3 (id INT PRIMARY KEY, CONSTRAINT c3 CHECK (id<50), CONSTRAINT c3 CHECK (id<40));

2. What did you expect to see? (Required)

MySQL 8.2.0

sql> CREATE TABLE t3 (id INT PRIMARY KEY, CONSTRAINT c3 CHECK (id<50), CONSTRAINT c3 CHECK (id<40));
ERROR: 3822 (HY000): Duplicate check constraint name 'c3'.

3. What did you see instead (Required)

sql> SET GLOBAL tidb_enable_check_constraint=ON;
Query OK, 0 rows affected (0.0029 sec)

sql> CREATE TABLE t3 (id INT PRIMARY KEY, CONSTRAINT c3 CHECK (id<50), CONSTRAINT c3 CHECK (id<40));
ERROR: 1061 (42000): duplicate key name c3

4. What is your TiDB version? (Required)

Release Version: v7.6.0-alpha-23-gdfd9080bcf-dirty
Edition: Community
Git Commit Hash: dfd9080bcf49083e52d6cb714668da9b881dc54d
Git Branch: master
UTC Build Time: 2023-10-27 06:35:10
GoVersion: go1.21.0
Race Enabled: false
Check Table Before Drop: false
Store: unistore

[dveeden]

Another small issue with related errors:

MySQL 8.2.0

sql> CREATE TABLE t1(id INT PRIMARY KEY, KEY `foo` (id), KEY `foo` (id));
ERROR: 1061 (42000): Duplicate key name 'foo'

TiDB:

sql> CREATE TABLE t1(id INT PRIMARY KEY, KEY `foo` (id), KEY `foo` (id));
ERROR: 1061 (42000): duplicate key name foo

The error code is correct, but duplicate isn't using a capital D and the name of the key isn't quoted.

Probably good to fix both in the same PR.

[dveeden]

Another difference:

MySQL 8.2.0

sql> CREATE TABLE t1 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.0961 sec)

sql> CREATE TABLE t2 (id INT PRIMARY KEY, c1 INT NOT NULL, c2 INT NOT NULL, KEY `foobar` (c1));
Query OK, 0 rows affected (0.0855 sec)

sql> ALTER TABLE t2 ADD CONSTRAINT `foobar` FOREIGN KEY (c2) REFERENCES t1(id);
ERROR: 1061 (42000): Duplicate key name 'foobar'

TiDB:

sql> CREATE TABLE t1 (id INT PRIMARY KEY);
Query OK, 0 rows affected (0.0326 sec)

sql> CREATE TABLE t2 (id INT PRIMARY KEY, c1 INT NOT NULL, c2 INT NOT NULL, KEY `foobar` (c1));
Query OK, 0 rows affected (0.0179 sec)

sql> ALTER TABLE t2 ADD CONSTRAINT `foobar` FOREIGN KEY (c2) REFERENCES t1(id);
ERROR: 1061 (42000): index already exist foobar