MySQL - 1075 Incorrect table definition, there can be only one auto column and it must be defined as a key

Question

I have the following code:

use Phinx\Migration\AbstractMigration;

class CreateNewTableForJobListLanguages extends AbstractMigration
{
    /**
    * Migrate up.
    */
    public function up()
    {
        $this->execute('
        CREATE TABLE `v2_joblist_languages` (
        `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT, 
        `joblist_id` int(11) NULL,
        `language_id` int(11) NULL,
        FOREIGN KEY (`joblist_id`) REFERENCES `v2_job_alerts` (`id`),
        FOREIGN KEY (`language_id`) REFERENCES `v2_languages` (`id`))
    ');
}

When trying to migrate, I get the following error:

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

Broke my head trying to solve it, and understand why is it happening. Grateful for any assistance, thanks in advance.

Possible duplicate of [MySQL: #1075 - Incorrect table definition; autoincrement vs another key?](https://stackoverflow.com/questions/8114535/mysql-1075-incorrect-table-definition-autoincrement-vs-another-key) — Can O' Spam, Jul 25 '18 at 15:33
As the error message states, the column `id` must be defined as your primary key. — Sloan Thrasher, Jul 25 '18 at 16:07
Thank you, that was the problem. Its my first week with MySQL, making basic mistakes. After assigning PRIMARY KEY(`id`) this mistake has disappeared. Create an answer so that I can approve it — guitarfreak, Jul 26 '18 at 06:44

score 0 · Answer 1 · answered Jul 25 '18 at 16:02

This is a many:many mapping table? Is there really any need for id? I doubt it; get rid of it.

Also provide PRIMARY KEY(joblist_id), language_id), INDEX(language_id, joblist_id)

Do you really want the two columns to be NULLable? I doubt it.

More tips on many:many: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

MySQL - 1075 Incorrect table definition, there can be only one auto column and it must be defined as a key

1 Answers1