0

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?

2
  • You could place the primary key on just the auto increment column. Then build a unique index on the four to accomplish your goal. Mar 7, 2014 at 20:44
  • Sorry, did you mean something like this: 4) the INT column which should be AUTO_INCREMENT will become the PRIMARY KEY of the table; 1), 2) fields still remain the FOREIGN KEYS; 1), 2), 3), 4) fields together become the index of the table (what command should I do? INDEX(col1, col2, col3, col4) --- or --- UNIQUE(col1,col2,col3,col4)? Sorry but I have never used such a technique. And one of the most important things: will mysql be fast when scrolling the records if I use a UNIQUE or INDEX instead of PRIMARY KEY for the 4 fields? Cause I would like it to be fast in Look Up queries. Mar 7, 2014 at 21:00

2 Answers 2

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.

1
  • The AI column does not have to be the PK. It does have to be the first column in some index.
    – Rick James
    Apr 8, 2022 at 6:13
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.