I get this: "error: #1075 - Incorrect table definition; There can only be one auto column and it must be defined as a key" when i try to create this bridge table between two other tables in a many-to-many. The first example creates two seperate autonumbered key fields in one table, which is not acceptable.
CREATE TABLE IF NOT EXISTS temp_download (
userid INT UNSIGNED NOT NULL AUTO_INCREMENT,
dloadid INT UNSIGNED NOT NULL AUTO_INCREMENT,
FOREIGN KEY (tempid) REFERENCES temp_user(id) ON DELETE CASCADE,
FOREIGN KEY (dloadid) REFERENCES download(id) ON DELETE CASCADE
) TYPE=innodb;
CREATE TABLE IF NOT EXISTS temp_download (
userid INT UNSIGNED NOT NULL AUTO_INCREMENT,
dloadid INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (userid, dloadid),
FOREIGN KEY (tempid) REFERENCES temp_user(id) ON DELETE CASCADE,
FOREIGN KEY (dloadid) REFERENCES download(id) ON DELETE CASCADE
) TYPE=innodb;
How else am I supposed to do it? I am basically just trying to do this: http://articles.techrepublic.com.com/5100-6329-5034790.html in figure A: As far as I know, a composite primary key is only one key, correct?
Rock on.
