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:

a two-column primary key including the columns "de" and "location"
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

@Jens: Thanks for that. Think we got it sorted with the answers provided below. — keewee279
– keewee279, Commented Jun 17, 2015 at 7:59

AnkiiG · Accepted Answer · 2015-06-17 08:04:17Z

3

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)
    )

edited Jun 17, 2015 at 8:04

answered Jun 17, 2015 at 7:55

AnkiiG

3,4861 gold badge19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

keewee279 Over a year ago

Thanks a lot - this works great as well ! As the other answer seems to be the more general approach I decided to accept that one but voted you up as well, hope that's ok. Thanks again !

Venkatesh Panabaka · Accepted Answer · 2015-06-17 07:59:21Z

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))

edited Jun 17, 2015 at 7:59

answered Jun 17, 2015 at 7:58

Venkatesh Panabaka

2,1644 gold badges21 silver badges27 bronze badges

2 Comments

keewee279 Over a year ago

Thanks for this as well ! It works well with the answer from user1151316.

keewee279 Over a year ago

I voted you up already. :) I tested this and it works great. As this seems to be the more general approach I decided to accept this answer. Thanks again.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related