dolthub / dolthub/dolt

auto_increment columns not properly invalidated when part of composite key

Open
#9,896 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

Dolt correctly invalidates auto_increment columns when it's not part of a key but does not when it's part of a composite key. This behavior differs from MySQL.

Dolt
```
tmp/main*> create table parent (id int not null auto_increment);
there can be only one auto_increment column and it must be defined as a key
tmp/main*> create table parent (id int not null auto_increment, primary key (id));
Query OK, 0 rows affected (0.01 sec)
tmp/main*> create table child (parent_id int not null, version_id int not null auto_increment, primary key (parent_id, version_id), constraint fk_child_parent foreign key (parent_id) references parent (id));
Query OK, 0 rows affected (0.01 sec)
```

MySQL
```
mysql> create table parent(id int not null auto_increment, primary key (id));
Query OK, 0 rows affected (0.00 sec)

mysql> create table child(parent_id int not null, version_id int not null auto_increment, primary key(parent_id, version_id), constraint fk_child_parent foreign key (parent_id) references parent(id));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
```

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.