Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You could place the primary key on just the auto increment column. Then build a unique index on the four to accomplish your goal. –  Adam Zuckerman Mar 7 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. –  user3019105 Mar 7 at 21:00
add comment

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Thank you, you have been very clear and precise! –  user3019105 Mar 7 at 21:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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