SQL Error (3750): Unable to create or change a table without a primary key

MySQL
1

Hello i’m new in aiven, and today i try free plan of mysql database. i successfully connect to my database using heidiSQL. now i want to run my sql file to export my table from my local database…but i found some error it says Unable to create or change a table without a primary key.

How to fix this? because i try import and export in localhost is working normally

2

Hi there,

Based on this product documentation, it appears that Aiven does not allow created new tables without primary keys by default. If necessary, this can be changed and you can create missing primary keys if needed.

However, per the docs,

It is only recommended to use this approach when the table is created by an external application and using the session variable is not an option. To prevent more problematic tables from being unexpectedly created in the future you should enable the setting again once you finished creating the tables without primary keys.

1 Like
3

Tacking onto @kjaymiller’s response, Create missing primary keys | Aiven docs has some instructions on how to add a primary key on your table, should you choose to go that route. :slight_smile:

4

To fix this issue, you need to ensure that your table has a primary key defined before importing it into your Aiven MySQL database.

  1. Open your SQL file in a text editor.
  2. Locate the table creation statement within the SQL file.
  3. Ensure that one of the columns in the table is designated as the primary key using the PRIMARY KEY constraint. If none of the columns are suitable as a primary key, you may need to add a new column specifically for this purpose.
CREATE TABLE example_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    column1 VARCHAR(255),
    column2 INT,
    ...
);
  1. Save the changes to the SQL file.
  2. Retry importing the SQL file into your Aiven MySQL database using HeidiSQL.

By adding a primary key to your table, you should be able to successfully import it into your Aiven MySQL database without encountering the “Unable to create or change a table without a primary key” error.

Best Regard
Danish Hafeez | QA Assistant
ICTInnovations