Error when adding auto-increment and primary key to existing MySQL table

[lukekalish]

Describe the bug You have a pre-existing MySQL table with an id column that is an int and not auto incrementing or a primary key. You alter the table to make the id column auto-incrementing and a primary key; the SQL produced is two statements. The first is altering the column to be auto-incrementing, and the second statement is to make the column the primary key. But MySQL will throw an exception on the first statement, stating that an auto-incrementing column must also be a primary key. This should be a single alter statement that adds the auto-incrementing and the primary key.

To Reproduce Steps to reproduce the behavior:

1. Create a table with a column named "test_id" and is an int non-nullable
2. Right-click the table and select alter
3. Check the primary and auto-increment check boxes for the "test_id" column
4. Accept the changes, notice the two alter statements
5. See produced error

Expected behavior Instead of two separate statements, there should be a single alter statement to make the column auto-increment and the primary key. This single statement does not result in an error.

Example: ALTER TABLE test_table MODIFY test_id int AUTO_INCREMENT NOT NULL PRIMARY KEY;

Screenshots

Environment details (please complete the following information):

• Device: MacBook Pro M1
• OS: macOS 14
• SQLPro Studio: Version 2023.35 (Build 111030)
  • Installation source: Website download
  • Target database server: MySQL

Additional context N/A

[hankinsoft]

Thank you - investigating this for the next build.

[hankinsoft]

If you get the chance, could you try https://sqlprostudio.s3.us-east-1.amazonaws.com/mysql/SQLProMySQL.2024.16.app.zip? That should sort this out.

[hankinsoft]

Going to mark this as closed. If you continue to have any issues with it, please let me know.