Originally Posted by

**shaunthomson**
Can you use three columns that are unique together as the primary key, or is that not workable?

*SIGH*

Didn't we have this discussion already.

Didn't we talk about "skipping" fields in multi-field indexes?

Didn't I even suggest that a good primary key would be

Code:

CREATE TABLE djBooks (
djid INT,
songid INT,
bookNum TINYINT,
PRIMARY KEY( djid, booknum, songid )
);

And didn't I note the reasons for putting the fields in that order in the (yes, three field) primary key?

I even gave the examples:

-- a WHERE involving just DJID will use the index

-- a WHERE involving DJID and BOOKNUM will use the index

-- a WHERE involving all three fields will use the index

BUT:

-- a WHERE involving just BOOKNUM will *NOT* use the index

-- a WHERE involving just SONGID will *NOT* use the index

-- a WHERE involving just BOOKNUM and SONGID will *NOT* use the index

AND:

-- a WHERE invovling just DJID and SONGID will use the index only for the DJID.

(All the above may not be true in other databases--SQL Server in particular--but it is a well document limitation of MySQL.)