Table is still created when `CREATE TABLE` errors out due to invalid foreign key
- 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.