OK I'm so confused about composite primary keys. I know exactly what they are and what they do in *theory*, but I'm having trouble implementing them.

I know that multiple columns in a table make up a composite primary key. Problem is, how do I retrieve a composite primary key if it's made up of so many columns? Or do I need an EXTRA column that combines the results of all the columns into a single string?

Please help >.<

Help very much appreciated!

P.S What's the difference between "unique" and "primary" keys? I thought they were the same thing, and then someone says to me that I need to make my primary keys unique??? I thought by declaring a column a primary key in mySQL (I use auto-increment btw) puts a primary key constraint on the column ... which ensures that no 2 PKs are the same!

PPS I know some people hate compound keys and other swear by them, but I really need to understand this

retrieve an index

SHOW INDEX FROM table FROM db;

Wikipedia explains primary & unique keys quite well http://en.wikipedia.org/wiki/Unique_key

Originally Posted by deltawing1

I know that multiple columns in a table make up a composite primary key. Problem is, how do I retrieve a composite primary key if it's made up of so many columns? Or do I need an EXTRA column that combines the results of all the columns into a single string?

That would be counter productive; the idea is to keep each data row as small as possible to use less disc I/O, since it's one of the slowest thing a database server has to do. An index is like an external copy of part of the data, with bookmarks to the row each entry represents. They're stored as binary trees, which are much faster to look for particular data in.

Create Index idxName On TableName ( Column_A, Column_B, Column_C )

A query that uses Column_A and Column_B in a where or join clause will benefit from this index, but a query that only uses Column_C probably won't. On the other hand, a query that only uses the columns in the index will run without ever touching the table itself ... at least in Microsoft land.

Originally Posted by deltawing1

P.S What's the difference between "unique" and "primary" keys? I thought they were the same thing, and then someone says to me that I need to make my primary keys unique??? I thought by declaring a column a primary key in mySQL (I use auto-increment btw) puts a primary key constraint on the column ... which ensures that no 2 PKs are the same!

In practical terms, you can have a dozen unique keys, but only one PK. Also, you can have a null value in a unique key if you choose to.