Does MySQL allow two primary keys on one table?

Question

CREATE TABLE Orders
-> (
->    ID SMALLINT UNSIGNED NOT NULL,
->    ModelID SMALLINT UNSIGNED NOT NULL,
->    Descrip VARCHAR(40),
->    PRIMARY KEY (ID, ModelID)
-> );

Basically, this appears to me to be creating two primary key on one table. Is that correct?

I thought that we could create a number of unique keys in one table, but only one primary key.

How is it that my system is allowing the creation of multiple primary keys?

Please advise: what are the rules governing this?

Answer 1

You can have one primary key (thats why it is called the primary key)

You can have multiple UNIQUE keys if you like.

Answer 2

Your system is not allowing multiple primary keys - it is creating the key based on 2 columns (ID, ModelID)

Answer 3

You are making 1 primary key. But that key is a combination of 2 values.

Which is not a wrong thing to do. But in your case it does look wrong.

You seem to have a primary key named ID and a foreign key named ModelID. You should probable have an index on the ModelID, and a primary key constraint on the ID

Answer 4

Yes, this is a Composite Key.

Answer 5

Think of it like it suggest, a 'KEY'. So the key would be all of the columns specified. In your case you can have multiple rows with the same 'ID' and multiple rows with the same 'ModelID' but there shall never be two rows that have the same 'ID' AND 'ModelID'.

So in this case it is not saying that the column 'ID' must be unique nor is it saying that 'ModelID' must be unique, but only the combination.