Auto Increment in Temporary Table

Question

I'm trying to create a Temp Table that has a key field that auto increments. But I keep running into a syntax error.

Here's what I'm trying:

CREATE TEMPORARY TABLE
    RETURN_ARTISTS
        (KEY INT(11) NOT NULL AUTO_INCREMENT,
        ARTIST_1_KEY INT(11),
        ARTIST_2_KEY INT(11));

INSERT INTO RETURN_ARTISTS (1,1);

DELETE TABLE RETURN_ARTISTS;

And here's the error I keep getting:

Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INT(11) NOT NULL AUTO_INCREMENT,
        ARTIST_1_KEY INT(11),
        ARTIST_2_KEY INT(' at line 3

score 5 · Accepted Answer · answered Apr 17 '12 at 03:00

First of all, key is a reserved word, escape it with `

Secondly, when using auto_increment column, that column must be defined as a key.

CREATE TEMPORARY TABLE
`RETURN_ARTISTS`
    (`KEY` INT(11) NOT NULL AUTO_INCREMENT,
    ARTIST_1_KEY INT(11),
    ARTIST_2_KEY INT(11),
    KEY (`KEY`));

score 1 · Answer 2 · answered Apr 17 '12 at 02:50

1

key is a reserved word, so you'll have to escape it:

    (`KEY` INT(11) NOT NULL AUTO_INCREMENT,
     ^---^--- escapes

or, preferably, use a different field name.

answered Apr 17 '12 at 02:50

Marc B

356,200
43
426
500

Auto Increment in Temporary Table

2 Answers2

Linked