setting two foreign keys to associate with the same field, deleting data is normal but does not take effect
Closed this issue · 6 comments
I created a simple script to reproduce the problem
func main() {
db := memory.NewDatabase("testdb")
db.BaseDatabase.EnablePrimaryKeyIndexes()
pro := memory.NewDBProvider(db)
session := memory.NewSession(sql.NewBaseSession(), pro)
ctx := sql.NewContext(context.Background(), sql.WithSession(session))
engine := sqle.NewDefault(pro)
createTableRow := `CREATE TABLE testdb.table_row (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL
);`
createTableRelation := `CREATE TABLE testdb.table_relation (
id INT PRIMARY KEY AUTO_INCREMENT,
source_row_id INT,
target_row_id INT,
CONSTRAINT table_relations_table_rows_source_relations FOREIGN KEY (source_row_id) REFERENCES testdb.table_row(id) ON DELETE CASCADE,
CONSTRAINT table_relations_table_rows_target_relations FOREIGN KEY (target_row_id) REFERENCES testdb.table_row(id) ON DELETE CASCADE
);`
_, _, err := engine.Query(ctx, createTableRow)
if err != nil {
log.Fatal(err)
}
_, _, err = engine.Query(ctx, createTableRelation)
if err != nil {
log.Fatal(err)
}
insertData := `INSERT INTO testdb.table_row (name) VALUES ('row1'), ('row2'), ('row3');`
_, row, err := engine.Query(ctx, insertData)
if err != nil {
log.Fatal(err)
}
r, err := row.Next(ctx)
if err != nil {
log.Fatal(err)
}
fmt.Println(r)
insertRelation := `INSERT INTO testdb.table_relation (source_row_id, target_row_id) VALUES (1, 2), (2, 3);`
_, relation, err := engine.Query(ctx, insertRelation)
if err != nil {
log.Fatal(err)
}
fmt.Println(relation.Next(ctx))
deleteData := `DELETE FROM testdb.table_row WHERE id = 1;`
_, d, err := engine.Query(ctx, deleteData)
if err != nil {
log.Fatal(err)
}
fmt.Println(d.Next(ctx))
_, rows, err := engine.Query(ctx, "SELECT * FROM testdb.table_row LIMIT 10;")
if err != nil {
log.Fatal(err)
}
rs, err := rows.Next(ctx)
fmt.Println(rs)
rs, err = rows.Next(ctx)
fmt.Println(rs)
rs, err = rows.Next(ctx)
fmt.Println(rs)
}The above code performs the following operations: Create tables table_row and table_relation. The table table_relation contains source_row_id and target_row_id and both are foreign keys to the id field of table_row. Then insert data into the table and delete the data in table_row.
As can be seen from the printed output, we want to delete the row with id 1. The SQL statement runs without exception, but the deletion is not successful.
HI @Mickls:
Sorry for the delayed response here.
I've tested this and it does seem to be a bug. We'll get it on our docket. Thanks for the report!
HI @Mickls:
Sorry for the delayed response here.
I've tested this and it does seem to be a bug. We'll get it on our docket. Thanks for the report!
I'm glad this issue has been responded to. In fact, it has caused many of my UTs to not run correctly, and I had to switch to other solutions. I would be very grateful if this problem can be solved.
If this is a bug in Dolt as well, we'll move it to that queue and fix it in the next couple days. If not, it will be fixed in the next week or so.
Hey @Mickls, thanks for reporting this issue!
I have a fix currently out for review, and we will let you know once it's merged.
Fortunately, this issue was somehow only apparent in GMS and not dolt.