dolthub / dolthub/dolt

Table is still created when `CREATE TABLE` errors out due to invalid foreign key

Open
#11,082 0 comments 0 reactions 0 assignees View on GitHub
correctness sql
Dominant language
Go
Stars
24.4k
Forks
873
Avg merge
1d 5h
Merged PRs (30d)
108

Description

This issue was discovered while working on dolthub/go-mysql-server#3552. A `CREATE TABLE` statement with an invalid foreign key will correctly error out, but the table still ends up being created, just without the foreign key constraint.

Set up: `create table parent(id int primary key)`

Invalid `CREATE TABLE` statement: `create table child3(a int, b int as (a) stored, foreign key (a) references parent(id) on update cascade)`
ExpectedErr: `sql.ErrStoredGeneratedColumnForeignKeyConflict`

Follow up query: `create table child3(a int, b int as (a) virtual, foreign key (a) references parent(id) on update cascade);`
This should work since the foreign key is valid here (virtual instead of stored generated column), but this instead fails because `table with name child3 already exists`, but the first `child3` shouldn't have been created if the foreign key was invalid.

In MySQL, if a table has an invalid foreign key, it's not created.
```
mysql> create table child(a int, b int as (sqrt(a)) stored, foreign key (a) references parent(id) on update cascade);
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql> select * from child;
ERROR 1146 (42S02): Table 'db.child' doesn't exist
```

This issue seems to have been known for quite a while, as indicated by this [TODO comment from 2023](https://github.com/dolthub/go-mysql-server/blob/78fcb7d6e3d9b6006dc493c90cd4b421e613cc72/sql/rowexec/ddl.go#L1187).

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.