Adding/removing auto-incrementing int column to MariaDB/MySQL generates bad code

[JTMosaic]

Description

Adding or removing an auto-incrementing integer column to a table which does not have one via the table view generates code which cannot run without being edited.

DBeaver Version

24.2.0

Operating System

macOS

Database and driver

MariaDB 11

Steps to reproduce

1. Create a table with a varchar column
2. Edit the table via the GUI and add a new column like so:
3. Click OK. Notice the "Key" column for the new column is not populated
4. Click Save to get the following code:

ALTER TABLE mytable ADD id INT auto_increment NOT NULL;
ALTER TABLE mytable ADD CONSTRAINT `primary` PRIMARY KEY (id); 

5. Clicking Execute will generate an error:

   SQL Error [1075] [42000]: (conn=107406) Incorrect table definition; there can be only one auto column and it must be defined as a key
   (conn=107406) Incorrect table definition; there can be only one auto column and it must be defined as a key

The correct sql to add such a column is:

ALTER TABLE mytable ADD id INT auto_increment NOT NULL PRIMARY KEY

6. Once the column is added to the table, delete it via the GUI
7. Answer "Yes" to both questions
8. Click Save to get the following code:

   ALTER TABLE mytable DROP PRIMARY KEY;
   ALTER TABLE mytable DROP COLUMN view_id;

9. Clicking Execute will generate an error:

SQL Error [1075] [42000]: (conn=107406) Incorrect table definition; there can be only one auto column and it must be defined as a key
  (conn=107406) Incorrect table definition; there can be only one auto column and it must be defined as a key

The correct way to drop such a column is to drop the column first, then the primary key:

ALTER TABLE mytable DROP COLUMN view_id;
ALTER TABLE mytable DROP PRIMARY KEY;

Additional context

No response

[E1izabeth]

Thanks for the bug report. I reproduced the issue