Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT?

Question

I have a complex primary key, I wonder if what I write in the title is possible:

I have a look up table with 4 fields (they should compose the primary key of the table):

1) an INT column;

2) Another INT column;

3) A YEAR column;

4) another INT column which should be AUTO_INCREMENT.

The 1) and 2) fields are also FOREIGN KEYS of another table, and I need them to be so.

How can I handle this?

Answer 1

You can do this:

CREATE TABLE mytable
(
    `ID` MEDIUMINT NOT NULL AUTO_INCREMENT,
    `C1` MEDIUMINT NOT NULL,
    `C2` MEDIUMINT NOT NULL,
    `Year` MEDIUMINT NOT NULL,
    PRIMARY KEY (ID),
    FOREIGN KEY (C1, C2)
            REFERENCES othertable(pkcol1, pkcol2)
);

ALTER TABLE mytable ADD UNIQUE INDEX(`C1`, `C2`, `Year`, `ID`);

Adding the ID column to the unique index will guarantee that you can have the same values for C1, C2 and Year in your table. If you don't want that, remove ID from the ALTER TABLE statement.

The syntax of the FOREIGN KEY is for a multiple column key in another table called othertable. pkcol1 and pkcol2 would be the corresponding columns in that table.

ID would be your fourth column in the question example code you presented.

Answer 2

It is quite OK to have a composite PRIMARY KEY, whether or not one of the columns is AUTO_INCREMENT. In fact, there is a good use case for doing such.

Here's the use case:

• The table is large (bigger than innodb_buffer_pool_size -- hence random fetches might require disk I/O)
• The application queries tend to fetch several of the rows for a given "user_id". (Example: The table is a list of purchases made by users.)
• The rows for a given user will come in sporadically over time. (Hence, the rows for a user are rarely located 'together.)
• InnoDB orders the table by the PRIMARY KEY. (fact)
• Disk I/O, when needed, is likely to be the slowest part of the query. (fact)

If an auto_inc were used as the PK, the rows for a given user would be scattered around the table. This would make the query slower, possibly even requiring multiple disk fetches.

If, instead, the rows for a user were "clustered" together by having the PRIMARY KEY start with user_id, then all the rows for that user might be in a single (or very few) block, thereby making disk caching more effective.

In this example, the following is advised:

id ... AUTO_INCREMENT,
user_id ...,
...
PRIMARY KEY(user_id, id),  -- provides the clustering
INDEX(id)  -- This is necessary and sufficient to keep auto_inc happy.

Your 4-column PK is not any different.