CODEWITHSUNDEEP
sqlmysql
Bharanikumar
Bharanikumar

Reputation: 25733

Does MySQL allow two primary keys on one table?

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?

Upvotes: 1

Views: 4906

Answers (4)

galford13x
galford13x

Reputation: 2531

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.

Upvotes: 1

Cine
Cine

Reputation: 4402

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

Upvotes: 1

Geek Num 88
Geek Num 88

Reputation: 5312

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

Upvotes: 3

Mitch Dempsey
Mitch Dempsey

Reputation: 39939

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

You can have multiple UNIQUE keys if you like.

Upvotes: 0

Related Questions