pingcap / pingcap/tidb

`CHECK` constraint may have inconsistency metadata between the memory and storage after several DDL

Open
#71,057 1 comment 0 reactions 1 assignee Assigned to @wjhuang2016 View on GitHub
affects-25.10 affects-26.3 affects-7.5 affects-8.1 affects-8.5 found-by-ai severity/major sig/sql-infra type/bug
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Bug Report

Please answer these questions before submitting your issue. Thanks!

### 1. Minimal reproduce step (Required)

1. Enable check constraints globally, reconnect, and prepare two schemas:

```sql
SET GLOBAL tidb_enable_check_constraint=1;
```

```sql
CREATE DATABASE like_check_a;
CREATE DATABASE like_check_b;
CREATE TABLE like_check_a.base (
id INT PRIMARY KEY,
a INT,
b INT,
CONSTRAINT base_chk_1 CHECK (a>0),
CONSTRAINT base_chk_2 CHECK (b>0)
);
ALTER TABLE like_check_a.base DROP CHECK base_chk_1;
ALTER TABLE like_check_a.base ADD CHECK (a>0);
SHOW CREATE TABLE like_check_a.base;
-- base_chk_2 is b>0; base_chk_1 is a>0.
```

2. Clone the table into another schema and immediately inspect the source again:

```sql
CREATE TABLE like_check_b.base LIKE like_check_a.base;
SHOW CREATE TABLE like_check_a.base;
-- The source now appears as base_chk_1 on b>0 and base_chk_2 on a>0.
```

3. Drop the constraint that `SHOW CREATE TABLE` says is attached to `b>0`, then test both predicates:

```sql
ALTER TABLE like_check_a.base DROP CHECK base_chk_1;
SHOW WARNINGS;
SHOW CREATE TABLE like_check_a.base;

INSERT INTO like_check_a.base VALUES (1,-1,1);
INSERT INTO like_check_a.base VALUES (2,1,-1);
```

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

`CREATE TABLE ... LIKE` must not modify the source table's metadata. `DROP CHECK base_chk_1` must operate on the exact constraint currently displayed under that name. In the sequence above, dropping the displayed `b>0` constraint should leave `a>0` enforced.

### 3. What did you see instead (Required)

The cross-schema `CREATE TABLE ... LIKE` changes the source table's in-memory check-constraint names without changing its persisted metadata. The following `DROP CHECK base_chk_1` resolves the name against the changed cache but the DDL worker applies it to the persisted constraint with that name. It silently drops `a>0`, not the displayed `b>0`. Consequently `(1,-1,1)` is accepted while `(2,1,-1)` is rejected by `base_chk_2`. The same divergence makes `ALTER CHECK ... NOT ENFORCED` disable a different constraint from the one named by the user.

### 4. What is your TiDB version? (Required)

```text
Release Version: v8.5.7
Edition: Community
Git Commit Hash: 202b7f47286a1109b5c957401d34c9358d130ae0
Git Branch: HEAD
Race Enabled: false
Store: tikv
```

The wrong-target DDL consequence was also reproduced at commit `1dcb34947971e6265e5a2c3610146e0c44978866`.

Contributor guide

Open the contributing guide

Research direction

Start by reproducing the SQL sequence involving CREATE TABLE ... LIKE, SHOW CREATE TABLE, DROP CHECK, and ALTER CHECK ... NOT ENFORCED. Trace how check-constraint metadata is copied and resolved by the DDL worker, then verify that cloning leaves the source unchanged and that each named operation targets the displayed constraint.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
50/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.