CODEWITHSUNDEEP
mysqlsqlnatural-key
John Dorean
John Dorean

Reputation: 3874

Can I use a non-numerical primary key for a MySQL table?

In my web application, the user can define documents and give them a unique name that identifies that document and a friendly name that a human will use to refer to the document. Take the following table schema as an example:

|   id   |    name        |   friendly_name   |
-----------------------------------------------
|    2   |    invoice-2   |   Invoice 2       |

In this example I've used the id column as the primary key, which is an auto incrementing number. Since there's already a natural ID for documents (name) I could also do this:

|    name        |   friendly_name   |
--------------------------------------
|    invoice-2   |   Invoice 2       |

In this example, name is the primary key of the document. We've eliminated the id field as it's essentially just a duplicate of name, since every document in the table must have a unique name anyway.

This would also mean that when I refer to a document from a foreign key relationship I'd have to call it document_name rather than document_id.

What's the best practice regarding this? Theoretically it's entirely possible for me to use a VARCHAR for the primary key, but does it come with any downsides such as performance overhead?

Upvotes: 3

Views: 3180

Answers (2)

spencer7593
spencer7593

Reputation: 108530

There are two schools of thought on this topic.

There are some who hold strongly to the belief that using a "natural key" as the primary key for an entity table is desirable, because it has significant advantages over a surrogate key.

The are others that believe that a "surrogate" key can provide some desirable properties which a "natural" key may not.

Let's summarize some of the most important and desirable properties of a primary key:

  • minimal - fewest possible number of attributes
  • simple - native datatypes, ideally a single column
  • available - the value will always be available when the entity is created
  • unique - absolutely no duplicates, no two rows will ever have the same value
  • anonymous - carries no hidden "meaningful" information
  • immutable - once assigned, it will never be modified

(There are some other properties that can be listed, but some of those properties can be derived from the properties above (not null, can be indexed, etc.)

I break the two schools of thought regarding "natural" and "surrogate" keys as the "best" primary keys into two camps:

1) Those who have been badly burned by an earlier decision to elect a natural key as the primary key, and

2) Those who have not yet been burned by that decision.

Upvotes: 4

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57690

Of course you can.

create table sometbl(
`name` varchar(250) NOT NULL PRIMARY KEY,
`friendly_name` varchar(400)
);

Time for accessing integer or varchar (unless its too long) key doesn't have any difference. Even if it has, it wont be your main bottleneck. As long as a column is declared as key mysql can access it very fast.

Auto incrementing integer can not be primary key. Its just a serial number for the row. When you look at the real object you'll see it doesn't have any serial number. So the primary key should be based on those real properties.

Upvotes: 0

Related Questions