Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

TRUNCATE with foreign keys doesn't always work #5007

Closed
PavelSafronov opened this issue Dec 16, 2022 · 0 comments · Fixed by dolthub/go-mysql-server#1481
Closed

TRUNCATE with foreign keys doesn't always work #5007

PavelSafronov opened this issue Dec 16, 2022 · 0 comments · Fixed by dolthub/go-mysql-server#1481
Assignees
Labels
bug Something isn't working sql Issue with SQL

Comments

@PavelSafronov
Copy link
Contributor

TRUNCATE seems to ignore FOREIGN_KEY_CHECKS = 0

Repro SQL:

USE db;

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

INSERT INTO parent VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
INSERT INTO child VALUES (90, 1), (92, 3);

SELECT COUNT(*), "parent-table" FROM parent;

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE parent;

SELECT COUNT(*), "parent-table" FROM parent;

Dolt errors out with the above SQL:

Database changed
Query OK, 10 rows affected (0.00 sec)
Query OK, 2 rows affected (0.00 sec)
+----------+--------------+
| COUNT(*) | parent-table |
+----------+--------------+
| 10       | parent-table |
+----------+--------------+

error on line 23 for query 
TRUNCATE TABLE parent: cannot truncate table parent as it is referenced in foreign key d6mupffb on table child
cannot truncate table parent as it is referenced in foreign key d6mupffb on table child

MySQL handles above SQL without issues and empties the table:

+----------+--------------+
| COUNT(*) | parent-table |
+----------+--------------+
|       10 | parent-table |
+----------+--------------+
+----------+--------------+
| COUNT(*) | parent-table |
+----------+--------------+
|        0 | parent-table |
+----------+--------------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working sql Issue with SQL
Projects
None yet
Development

Successfully merging a pull request may close this issue.

2 participants