[MDEV-24598] duplicate CHECK constraint name Created: 2021-01-15  Updated: 2024-01-30

Status: In Review
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Sergei Golubchik Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-30899 Fail to drop CHECK constraint In Review
Relates
relates to MDEV-16630 Ambiguous error message when check co... Closed
relates to MDEV-24602 cannot specify a name for a column ch... In Review

 Description    

CREATE TABLE t (
 
path VARCHAR(100) NOT NULL COLLATE 'utf8_general_ci',
 
json_c1 JSON NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
 
json_c2 JSON NOT NULL DEFAULT '' COLLATE 'utf8mb4_bin',
 
comment VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
 
UNIQUE INDEX path (path),
 
CONSTRAINT json_c1 CHECK (path > 0)
 
)
 
;
 
select * from information_schema.check_constraints where table_name='t';

another example

create table t (a int check (a>2), constraint a check (a < 5));
 
select * from information_schema.check_constraints where table_name='t';



 Comments   
Comment by Anel Husakovic [ 2021-01-15 ]

Hi,
MDEV-16630 distinguished between field and table check constraints based on inserts.
Since I_S.check_constraints is used just for printing from tables->table->check_constraints, how we should approach to this?
To raise the error when selecting from I_S.check_constraints in case there are duplicate check constraints?

Comment by Sergei Golubchik [ 2021-01-15 ]

Well, MDEV-16630 was fixed incorrectly and should be reverted.
Constraint names must be unique within a table.
CREATE or ALTER TABLE must fail with two constraints happen to have the same name.

Otherwise how would you interpret, for example, ALTER TABLE t1 DROP CONSTRAINT b ?

Comment by Anel Husakovic [ 2021-01-15 ]

Make sense I understand now.
Started working on MDEV-24598, MDEV-24601, MDEV-24602.

Comment by Anel Husakovic [ 2023-11-16 ]

Related to the PR 2773

Comment by Anel Husakovic [ 2024-01-29 ]

Added commit ffd3ae247c9 as part of PR 2773.
Rebased on 10.5 https://github.com/an3l/server/commits/bb-10.5-anel-MDEV-24602-field-constraint-v5/

Generated at Thu Feb 08 09:31:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.