MySQL: How to create table with auto incrementing column via script

Question

I am new to MySQL and hope someone can help me with this.

I want to create a simple table with two special settings:

1. a two-column primary key including the columns "de" and "location"
2. an auto incrementing column "tID" that generates a unique ID, starting with 1

I tried the following (and several other approaches) but this always returns the below error:

The SQL:

CREATE TABLE TranslationsMain (
    de VARCHAR(100) NOT NULL,
    tID INT UNSIGNED NOT NULL AUTO_INCREMENT,
    location VARCHAR(50) NOT NULL,
    classes VARCHAR(100) NOT NULL,
    info VARCHAR(100) NOT NULL,
    sortOrder INT NOT NULL,
    en VARCHAR(100) NOT NULL,
    PRIMARY KEY(de, location)
)

The error message:

"Incorrect table definition; there can be only one auto column and it must be defined as a key."

It works if I leave the auto incrementing column ("tID") out so there seems to be something wrong here. Can someone help me with this ?

Many thanks in advance, Mike

Answer 1

In your SQL what the problem is auto increment must be primary key. Otherwise it will not work.

You can use like these. i think your problem will solve.

  CREATE TABLE TranslationsMain (
     de VARCHAR(100) NOT NULL,
     tID INT UNSIGNED NOT NULL AUTO_INCREMENT,
     location VARCHAR(50) NOT NULL,
     classes VARCHAR(100) NOT NULL,
     info VARCHAR(100) NOT NULL,
     sortOrder INT NOT NULL,
     en VARCHAR(100) NOT NULL,
     PRIMARY KEY(tID,de, location))

Answer 2

Try below query, I think this will solve your problem

CREATE TABLE TranslationsMain (
 de VARCHAR(100) NOT NULL,
 tID INT UNSIGNED NOT NULL AUTO_INCREMENT,
 location VARCHAR(50) NOT NULL,
 classes VARCHAR(100) NOT NULL,
 info VARCHAR(100) NOT NULL,
 sortOrder INT NOT NULL,
 en VARCHAR(100) NOT NULL,
 PRIMARY KEY(tID),
 UNIQUE(de, location))

OR

CREATE TABLE TranslationsMain12 (
     de VARCHAR(100) NOT NULL,
     tID INT UNSIGNED NOT NULL AUTO_INCREMENT,
     location VARCHAR(50) NOT NULL,
     classes VARCHAR(100) NOT NULL,
     info VARCHAR(100) NOT NULL,
     sortOrder INT NOT NULL,
     en VARCHAR(100) NOT NULL,
     unique(tID),
     primary key(de,location)
    )