sql single primary key

Question

While reading articles on w3schools about SQL primary keys I read the following:

Each table should have a primary key, and each table can have only ONE primary key.

http://www.w3schools.com/sql/sql_primarykey.asp

Yet I have this SQL file, for making a table, which I ran and worked:

CREATE TABLE accessLog (
  memberId        SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  pageUrl         VARCHAR(255) NOT NULL,
  numVisits       MEDIUMINT NOT NULL,
  lastAccess      TIMESTAMP NOT NULL,
  PRIMARY KEY (memberId, pageUrl)
);

Now according the the primary key quote above, the line of code: " PRIMARY KEY(memberId, pageUrl)," should not have worked.

Any help on this about how you can have more than ony primary key in a table. Note:I already know about the "UNIQUE", "UNIQUE KEY" statement.

This may interest you : http://stackoverflow.com/questions/11586986/advantages-and-disadvantages-of-having-composite-primary-key — AllTooSir, Apr 12 '13 at 15:40
You can have only **ONE** primary key - but that **ONE** primary key can be made up from multiple columns. The rule just is: once you've defined a primary key (based on a single or on multiple columns), you cannot add yet another primary key definition to the same table. — marc_s, Apr 12 '13 at 15:44

score 5 · Accepted Answer · edited May 23 '17 at 12:28

5

This is a composite primary key - it's still one key, but it's comprised of multiple columns. Here's a good, short description of composite primary keys.

edited May 23 '17 at 12:28

Community

1
1

answered Apr 12 '13 at 15:30

zimdanen

5,508
7
44
89

score 2 · Answer 2 · answered Apr 12 '13 at 15:31

2

It is a composite primary key . When you define more than one column as your primary key on a table, it is called a composite primary key.

answered Apr 12 '13 at 15:31

AllTooSir

48,828
16
130
164

Charles Bretana · Answer 3 · 2013-04-21T15:40:05.780

Your quote

"Each table should have a primary key, and each table can have only ONE primary key."

is misleading. It's equivalent to saying that each state can have only one capital city, or that each company can have only one Chief executive officer.

Of course each table can have only one PRIMARY key. But any table can have multiple Unique Keys. And, frankly, does not have to have any one of them designated as "PRIMARY". In fact designating one key as PRIMARY key does absolutely nothing at all. There is only one distinction (except as noted below) attached to the Primary Key which is not also associated with all keys.

EDIT ... except for one small distinction. If a key is designated as the Primary key, then all the fields used in that key must be non-nullable. Other Unique keys are not required to honor this rule, although rows in the table which contain nulls must still be unique, in that there cannot be more thab one row with the same values for all the non-nullable field and a null value in a nullable field.

Other than a "normal" unique key, the primary key **MUST** be NON NULLABLE - that's the main distinction — marc_s, Apr 12 '13 at 15:50
Not permitting nulls isn't a distinguishing feature of any one "primary" key. All candidate keys must be irreducibly unique and consist only of values, not nulls. That's what makes them keys. Historical note: E.F.Codd originally used the term "primary key" for *every* key of a relation, not just one key. Modern usage is to call them candidate keys. There's no difference however because candidate key means exactly the same thing as primary key. "Unique key" is a pointless tautology and a term that's probably best avoided. Keys are always unique. — nvogel, Apr 22 '13 at 15:05

sql single primary key

3 Answers3