pingcap / pingcap/tidb

`LOAD DATA` bypasses the foreign key checks

Open
#71,053 0 comments 0 reactions 0 assignees View on GitHub
found-by-ai may-affects-25.10 may-affects-26.3 may-affects-7.5 may-affects-8.1 may-affects-8.5 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. Create a parent/child pair and verify that ordinary `INSERT` enforces the child-side foreign key:

```sql
CREATE DATABASE load_fk;
USE load_fk;
CREATE TABLE parent (id INT PRIMARY KEY, u INT UNIQUE, v INT);
CREATE TABLE child (
id INT PRIMARY KEY,
pid INT,
FOREIGN KEY (pid) REFERENCES parent(id)
);
INSERT INTO parent VALUES (1,5,100);
INSERT INTO child VALUES (10,999);
-- Expected control: ERROR 1452
```

2. Create `/tmp/load-fk-child.csv` with this content and load it:

```text
10,999
11,999
```

```sql
LOAD DATA LOCAL INFILE '/tmp/load-fk-child.csv'
INTO TABLE child FIELDS TERMINATED BY ',';
SHOW WARNINGS;
SELECT COUNT(*)
FROM child c LEFT JOIN parent p ON c.pid=p.id
WHERE p.id IS NULL;
```

3. Reset the tables and verify the parent-side `REPLACE` path:

```sql
TRUNCATE TABLE child;
DELETE FROM parent;
INSERT INTO parent VALUES (1,5,100);
INSERT INTO child VALUES (10,1);

REPLACE INTO parent VALUES (2,5,200);
-- Expected control: ERROR 1451 because u=5 conflicts with the referenced row id=1.
```

4. Create `/tmp/load-fk-parent.csv` containing `2,5,200`, then run:

```sql
LOAD DATA LOCAL INFILE '/tmp/load-fk-parent.csv' REPLACE
INTO TABLE parent FIELDS TERMINATED BY ',';
SHOW WARNINGS;
SELECT * FROM parent;
SELECT * FROM child;
SELECT COUNT(*)
FROM child c LEFT JOIN parent p ON c.pid=p.id
WHERE p.id IS NULL;
ADMIN CHECK TABLE child;
```

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

`LOAD DATA` should enforce the same foreign-key checks and reference actions as `INSERT` and `REPLACE`. Loading a child row without a parent should fail with error 1452. `LOAD DATA ... REPLACE` should not delete a referenced parent row; for the default `RESTRICT` action it should fail with error 1451.

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

Both `LOAD DATA` statements succeed with no warnings. The first inserts two child rows whose parent does not exist. The second removes parent row `id=1`, inserts `id=2`, and leaves child row `(10,1)` orphaned. The same parent-side path also fails to execute declared `ON DELETE CASCADE` and `ON DELETE SET NULL` actions. `ADMIN CHECK TABLE child` still reports success, so TiDB has no built-in check here that exposes the referential-integrity violation.

This report is intentionally limited to `LOAD DATA`. It does not include `IMPORT INTO`, whose foreign-key behavior is already covered by an existing TiDB issue/documented restriction discussion.

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

```text
Release Version: v8.5.7
Edition: Community
Git Commit Hash: 202b7f47286a1109b5c957401d34c9358d130ae0
Git Branch: HEAD
UTC Build Time: 2026-07-09 23:07:02
GoVersion: go1.25.10
Race Enabled: false
Check Table Before Drop: false
Store: tikv
```

Contributor guide

Open the contributing guide

Research direction

Run the provided LOAD DATA, LOAD DATA ... REPLACE, SHOW WARNINGS, and ADMIN CHECK TABLE reproduction against TiDB, then compare those paths with the ordinary INSERT and REPLACE controls. Done means child loads reject missing parents with error 1452, parent replacement respects error 1451 or declared cascade/set-null actions, and no orphan rows remain.

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
Clearly specified
Newbie friendliness
48/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.