CODEWITHSUNDEEP
mysql
Robert
Robert

Reputation: 10390

primary key specifying two columns

I am learning about sql and saw the following:

create table order_items
( orderid int unsigned not null,
  isbn char(13) not null,
  quantity tinyint unsigned,

  primary key (orderid, isbn)

);

The line in question is: primary key (orderid, isbn)

How can you have two columns set as primary keys? How does this work? I understand having one column as a primary key like your social security, it's unique.

Upvotes: 0

Views: 32

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

A primary key is one or more columns that have the following two properties:

  • The values are unique.
  • The columns are not null.

This simply means that pairs of values of those two columns uniquely identify a row. A table can only have one primary key.

In my opinion, I prefer auto-incremented numeric primary keys, with a secondary (unique composite) index for these two columns. One reason is foreign key references, which I find easier to maintain with a single column.

Upvotes: 1

Related Questions