Composite key - one a foreign key, the other auto-incrementing

Question

I'm having trouble creating a table in MySQL. Essentially, I need a composite key using "client_id" and "problem_id". In the table "clients", "client_id" is the primary key so I want to keep this relationship, and "problem_id" needs to be auto incrementing.

CREATE TABLE problems (
client_id BIGINT(10) NOT NULL REFERENCES clients(client_id),
problem_id INT NOT NULL AUTO_INCREMENT,
status CHAR(1) NOT NULL,
description VARCHAR(100) NOT NULL,
start_date VARCHAR(10) NOT NULL,
end_date VARCHAR(10),
PRIMARY KEY (client_id, problem_id)
);

MySQL won't accept this, but it seems logical to me. How can I achieve such a table?

Answer 1

Two problems:

• InnoDB requires that the auto_increment column be the first column in your primary key.

• InnoDB does not support column-level REFERENCES syntax, it only supports table-level FOREIGN KEY constraint syntax.

This should work:

CREATE TABLE problems (
 client_id BIGINT(10) NOT NULL,
 problem_id INT NOT NULL AUTO_INCREMENT,
 status CHAR(1) NOT NULL,
 description VARCHAR(100) NOT NULL,
 start_date VARCHAR(10) NOT NULL,
 end_date VARCHAR(10),
 PRIMARY KEY (problem_id, client_id),
 FOREIGN KEY (client_id) REFERENCES clients(client_id)
);

However, this means that your clustered index (the primary key) is going to benefit lookups by problem_id, but not lookups by client_id.