1
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?

paxdiablo
  • 854,327
  • 234
  • 1,573
  • 1,953
Bharanikumar
  • 25,457
  • 50
  • 131
  • 201
  • Officially, you can have N number of *candidate* keys and, at most, one primary key. Candidate keys are enforced using a unique constraint where as primary key are enforced using a primary key constraint. – Thomas May 18 '10 at 03:36

4 Answers4

3

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

Geek Num 88
  • 5,264
  • 2
  • 22
  • 35
1

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

Cine
  • 4,255
  • 26
  • 46
1

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.

galford13x
  • 2,483
  • 4
  • 30
  • 39
0

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

You can have multiple UNIQUE keys if you like.

Mitch Dempsey
  • 38,725
  • 6
  • 68
  • 74