Why is naming a table's Primary Key column “Id” considered bad practice?

Because it will become a bear trap for the naive developer.

In my (limited) experience, it is rare that an app only has one table. So are you going to always name the Primary Key in all tables “ID”?

You do that, and I’ll bet you a beer (and/or your job) that very soon you will dozily write some SQL which uses the wrong ID field from the wrong table, or perhaps in your code you confuse the ID for table A with the ID for table B, and as a result screw the data rigid. Great way to create bugs that are a pain to diagnose.

So in your Owners table call it Owner_ID, in your Buildings table call it Building_ID, in your Invoices table call it Invoice_ID. You get the idea.

Prevent confusion. There’s too much of it in the world already.

It can be but it's not the best choice for a couple reasons. First is that it only guarantees uniqueness within that single instance. Two use cases immediately come to mind that screw that up. First is the merger/acquisition of another company and needing to integrate both systems into one. I guarantee if they both used auto numbers for PKs, you're going to have a lot of work to do to integrate the data. Second is the need to distribute the data across multiple servers. You don't want the Tokyo system to run transactions against the server in Denver so you set up a local database and merge the data later into the master system. Oops, duplicate PKs.

Another reason not to use them is human's inherent need to impose meaning on recognizable patterns. Who gets to be employee #1? Why is there a big gap in the sequence? What happens when you run out of numbers? We know that the number is supposed to be meaningless but good luck enforcing that with sequential numbers.

So what should you use? Well, definitely NOT user entered data like business keys. The first rule of PKs is they should never change, ever. Data entered by humans WILL have errors that need correcting.

The best solution is a GUID. Globally unique and highly unlikely to be duplicated and even if it is, the volume will be so low it will be easy to remedy across systems. No discernable patterns for humans to latch onto and definitely not something people will try to memorize even if they do see them.

Naming a primary key column id is not bad practice.

Naming all primary key columns id is bad practice.

Not all tables should have a single-column primary key with an auto-incrementing integer.

Some tables have a multi-column primary key. Every many-to-many table, for example.

CREATE TABLE BooksAuthored ( 
  book_id INT NOT NULL, 
  author_id INT NOT NULL, 
  PRIMARY KEY (book_id, author_id) 
); 

Some tables have a “natural key” instead of an auto-incrementing key.

CREATE TABLE States ( 
  state_abbr CHAR(2) PRIMARY KEY, 
  state_name VARCHAR(20) NOT NULL 
); 

Forcing every table to have a superfluous primary key column id INT AUTO_INCREMENT even when it doesn’t need it was a habit popularized by “opinionated” frameworks like Ruby on Rails. Their theory was that forcing every table to follow a pattern makes some coding tasks more consistent. Nevertheless, there are cases where forcing that pattern is inappropriate and makes other coding tasks more complex and inefficient.

The bad practice isn’t using id sometimes — it’s the insistence on arbitrary rules even when they’re not helpful.

Put me down as the almost always NO camp.

The problem with an auto-incremented column as Primary Key is honestly, it’s very easy to destroy database integrity with it and it can make testing a royal pain in the butt.

I’ve often seen people say, “well it’s good when you have no natural key and are just using it as some sort of lookup table.” Yeah, no.

I’ll give you an example loosely based on an issue I encountered in the real world. Client was storing some data, I believe it was colors being used in some sort of lookup table, so I’ll use that as an example.

So table looked something like:

PK  Color  Usage 
==  ====    ==== 
 1  Red     Foreground 
 2  White   Background 
 3  Yellow  Frame 

So inserts looked something like

Insert Color_LK (Color, Usage) Values ('Red','Foreground') 
Insert Color_LK (Color, Usage) Values ('White','Background') 
Insert Color_LK (Color, Usage) Values ('Yellow','Frame') 

Easy peazy.

But imagine two scenarios:

The first: The inserts get run twice in production. Now you have duplicate rows, but they’re not duplicate because the Primary Key says they’re not. There’s nothing to keep someone from entering the same data multiple times. Or worse different data

Insert Color_LK (Color, Usage) Values ('Blue','Foreground') 

Now which is is the Foreground?

Ah, but Greg, obviously my key is 1 for the foreground so the Blue value (which would be associated with 4 won’t matter).

But, you don’t know that do you? Because perhaps in Dev, the developer did

Insert Color_LK (Color, Usage) Values ('Red','Foreground') 
Insert Color_LK (Color, Usage) Values ('White','Background') 
Insert Color_LK (Color, Usage) Values ('Yellow','Frame') 

But in UAT the UI person insisted that they change the Foreground color to Blue. The developer decides to update their original script, so they remove the first insert and add a new one.

Insert Color_LK (Color, Usage) Values ('White','Background') 
Insert Color_LK (Color, Usage) Values ('Yellow','Frame') 
Insert Color_LK (Color, Usage) Values ('Blue','Foreground') 

The UIX signs off on it and this gets deployed to production.

So now in Dev you have

PK  Color  Usage 
==  ====    ==== 
 1  Red     Foreground 
 2  White   Background 
 3  Yellow  Frame 

But in UAT and Production

PK  Color  Usage 
==  ====    ==== 
 1  White   Background 
 2  Yellow  Frame 
 3 	Blue 	Foreground 

Will the REAL value for Foreground please stand up.

Now, before you object and say, “that’ll never happen” I can tell you from years of experience I’ve seen this sort of thing happen all the time with lookup tables that use auto incrementing primary keys.

Or I’ve seen, even worse:

PK  Color  Usage 
==  ====    ==== 
 1  Red     Foreground 
 2  White   Background 
 3  Yellow  Frame 
 4	Blue	Foreground 

Which has its own problems!

So, the solution:

Personally, I’d probably just make the Usage Column the primary key.

But if for some reason you insist on using integers for your primary key (and there can be arguments for and against that) then don’t use auto-incrementing. Hand code in the value. i.e. Foreground WILL ALWAYS BE 1, Background WILL always be 2., etc.

So your inserts become

Insert Color_LK (PK, Color, Usage) Values (1, 'Red','Foreground') 
Insert Color_LK (PK, Color, Usage) Values (2, 'White','Background') 
Insert Color_LK (PK, Color, Usage) Values (3, 'Yellow','Frame') 

Now you insure that no matter what environment, 1 is always Foreground, etc.

The real problem with having the primary key column (assuming that there is indeed a single column primary key) that is always named ‘id’ in every table is that when these keys are referenced in another table the name of the foreign key column in the dependent table will be different than the column that it references and I have a problem with that. I am a firm believer that an object’s name should be consistent in all contexts where possible (a table that has multiple foreign key references to the same table is an obvious “not possible” exception) and that each name should uniquely refer to only one object.

Call me rigid, but do I think that it is approaching insanity, and certainly provoking of confusion, when every primary key is just called ‘id’. I agree that calling the primary key of the person table person_id is a bit redundant, but that is what you will have to call it when you reference it as a foreign key, so just call it person_id in the person table as well!

I have the same objection to using generic column names like ‘account_num’ in multiple tables if the “account number” referred to in the general_ledger table is not the “account number” in the customer table is not the “account number” in the vendor table. When you reference those columns in other tables, you will either have to prepend the name with some prefix indicating which account number it refers to or there will be confusion because developers, for example, are not interested in parsing foreign key constraints! So I prefer “gl_account_num”, “cust_account_num”, and “vend_account_num” be used in the defining table and all references consistently when I design a database schema.

“Everyone has a unique name” ??

No they do not.

I have a (weird) namesake in the USA - apparently the guy has a fetish for inflatable reindeer. (no: I’m not making this up…). Has caused me some awkward moments in job interviews…

One good quality of primary keys is that they do not change. Changing the value of a primary key column is (relatively speaking) a lot of work: All references to it must change. And people change names all the time.

Also: Primary keys should not be excessively long. They should only be long “enough” to ensure uniqueness. And names can be long. Just look at this venerable person:

His name is Adolph Blaine Charles David Earl Frederick Gerald Hubert Irvin John Kenneth Lloyd Martin Nero Oliver Paul Quincy Randolph Sherman Thomas Uncas Victor William Xerxes Yancy Zeus Wolfeschlegel­steinhausen­bergerdorff­welche­vor­altern­waren­gewissenhaft­schafers­wessen­schafe­waren­wohl­gepflege­und­sorgfaltigkeit­beschutzen­vor­angreifen­durch­ihr­raubgierig­feinde­welche­vor­altern­zwolfhundert­tausend­jahres­voran­die­erscheinen­von­der­erste­erdemensch­der­raumschiff­genacht­mit­tungstein­und­sieben­iridium­elektrisch­motors­gebrauch­licht­als­sein­ursprung­von­kraft­gestart­sein­lange­fahrt­hinzwischen­sternartig­raum­auf­der­suchen­nachbarschaft­der­stern­welche­gehabt­bewohnbar­planeten­kreise­drehen­sich­und­wohin­der­neue­rasse­von­verstandig­menschlichkeit­konnte­fortpflanzen­und­sich­erfreuen­an­lebenslanglich­freude­und­ruhe­mit­nicht­ein­furcht­vor­angreifen­vor­anderer­intelligent­geschopfs­von­hinzwischen­sternartig­raum Sr. Even wikipedia refused to use the full name in the URL.

Many names can be spelled in numerous ways - e.g. many arabic names have multiple correct spellings in the latin alphabet.

Many people have multiple names. Not as in “first name”, “middle name” - but “different names they go by”. Quite legally.

Are you OK with primary keys in UTF-8? Because how else are you going to write محمد ?

And for some reason, you believe that everybody has a name!?

As a programmer, you must read - and understand Falsehoods Programmers Believe About Names. It should open your eyes.

This is the biggest religious debate in the database world. Some advocate that every table MUST use a surrogate key. Others that one MYST always use the natural key. I am more pragmatic myself.

A natural key is, well, natural to the data. I prefer to use the natural key when it makes sense. However when the natural key is a lengthy sequence of bytes, say if it is made up of multiple character columns, it can produce an index that is not ideal to use for detail-to-parent joins. Also using it as a foreign key can make related table rows wider than they might be. In those cases I would definitely go for adding an autoincrementing integer surrogate key as the primary key and make the natural key an alternate unique key.

Sometimes you can; if your application enforces unique usernames at sign up, then you might be able to use user name.

I still wouldn’t though. Use a surrogate key (like an integer from a sequence generator) and merely have the username as an indexed column you can look up (or a join).

The main problem with using any value from the real world is that even if you can guarantee uniqueness when the account is created, you cannot guarantee there will be no change.

It is very common for people to change surnames, especially on marriage for example.

Not only can this cause a conflict with an existing previously unique name, but it can wreak havoc with key relationships.

Just don’t do it.

“Is it always recommended to have an auto-incremented column as the PRIMARY KEY in a database?”

The key word in the question is “always.”

The short answer is, no. It is not always recommended to have an auto-incremented column as the primary key.

For example, you have an interim table, a table for a many to many join. The natural key is the primary key from each of the other tables. Let’s assume, just for our argument here, that neither of those is a natural key. Instead, each of them is an artificial key, auto-incremented as we’re discussing. Those two columns make the PK on the interim table. You’re suggesting, for no reason, to add a second key, just so that we can “always” have a auto-increment key? No. Makes not sense. Adds overhead, and makes the whole design cumbersome, plus, unless other tables are related to the interim table, that artificial key will never be used for any queries. It exists to do nothing. You still have to enforce the unique keys between the two tables to ensure the many-to-many relationship isn’t broken as data gets removed from the system.

This isn’t even getting into the natural key versus artificial key debate. Myself, I lean hard towards artificial keys are better, for a number of reasons. However, as in the example above, natural keys have a reason to be used. Plus, even if you have an artificial key, you still must enforce the natural key or you’re looking at the potential for bad data.

I always use auto numbering (auto increment) as a primary key.

This is why I prefer this:

1. It’s the most compact form, so it saves of data storage and performs better. The change of making mistakes when correcting something manually is also minimalised.
2. I always use the same name for the primary key field. When I have a file customer the keys name is customer_id. By doing this I never have to look up the fields name (and I already know the field type).
3. It’s a sequential way of numbering, so lower numbers are always older. This can be convenient when something went wrong from a certain moment etc.

I already use this method many years and never experienced any disadvantages. However there might be rare situations where looking for other solutions is a better idea.

The main disadvantage to this (that I'm aware of) is that all other indexes will reference the rows by your primary key, so you're increasing the size (and decreasing the block density) of all of your other indexes.

The other thing you want to be careful with is that it's generally preferable to write your rows in sequential/increasing primary key order -- and having lots of independent components to your primary key increases the chances that you're not doing that.

If either of these issues sounds like it may apply, you should consider adding an "id" column to your table that serves as your primary key, and adding a separate UNIQUE KEY index on what would have otherwise been your primary key.

However, if neither of these sounds like a concern, I think it's fine.

There's some more discussion here:
http://www.mysqlperformanceblog.com/2006/10/03/long-primary-key-for-innodb-tables/

No, just the opposite. Auto incrementing is how you avoid the relational model. You are mimicking a magnetic tape file! Did you ever read a book on RDBMS? Definition of the key is that it is a subset of attributes such that it is unique and not null for every row in a table. By absolute textbook definition, an auto increment can never be a key because it can never be a column. What attribute does it model? None! It has to do with physical storage, not a logical model.

This is also why we don't like to say primary key anymore. This declaration is a leftover from when SQL was built on magnetic tape files and disk storage systems, not logical models.

Use a UUID when your primary key values are generated in a decentralized way. In other words, if multiple apps are creating data and assigning identifying keys, without any ability for the apps to sync with each other to avoid using the same primary key value.

Example: data is created on users's clients (browsers and mobile devices) before posting the data back to a website. They are doing this concurrently, and have no way of knowing how many other clients are doing the same thing. So if they each have their own idea of the current primary key value, and just trying to use the next higher value, they're sure to conflict.

Using a UUID makes the chances of conflict near zero, because each client is generating a new random UUID in a sufficiently large domain (128 bits) that the chances of two clients generating the same value is insignificant (at least for the scale of data we work on today, in the early 21st century).

The downsides include:

• 128 bits is larger than 32 bits used for traditional integers, so the data requires more space. UUID's are often stored as strings of hex digits, so they take even more space.
• The bulk of primary keys is repeated as other tables contain foreign keys referencing the UUID.
• Inserting new data into a table randomly, instead of appending to the end, may be inefficient, depending on the implementation of the DBMS.
• UUID's take longer to type, so doing ad hoc queries or mocking up test data during development is a PITA.

Why?

I might make sense, but I need to understand why someone thinks it’s needed. Let’s take a different tack. You can add an IDENTITY property to a column. You can also have a SEQUENCE property on a column. You can have both. Or two SEQUENCE columns. Or three. Why? Why would you need that. One example might be, you want to expose a number for the people using the app, but, you, appropriately, don’t want the artificial primary key value to suddenly get meaning. So, instead, you add the SEQUENCE. Now there are two different counters, one with meaning, and one without.

Without the why, I can’t tell you whether or not it makes sense.

Asked to answer, but I really didn’t. Sorry.

Absolutely, yes! You can set a primary key on character columns. Imagine you've got a table of your favorite books. The title of the book, a character column, could serve as your primary key. Every book has a unique title, right? Well, it's not purely black and white though.

While using primary keys on character columns is indeed possible, it's not always the best pick. There may be performance issues when dealing with large datasets. See, text comparison takes more time than integer comparison.

Also, remember the off chance that two books might have the same title? That's a bummer, mate! You've got yourself a duplicate key situation. That's why in many scenarios, using a unique identifier like an integer can be a safer bet.

But hey, don't let that get you down. If you're sure that your character values are distinct, go ahead and set that primary key. Just be mindful of the potential hiccups. Get creative with your databases, and keep the questions coming - you're learning heaps!

No skilled database designer will EVER “ just <use> those columns as part of your primary key”!

One might create an index that started with the low cardinality column (say ‘gender’ or ‘marital_status’) that includes the primary key column(s) in addition, but that would be a separate index from the primary key constraint and its supporting index. The primary key, but its very definition, is the column or set of columns that uniquely identifies a record in a table. Adding an attribute that has nothing to do with identifying a row breaks that purpose.

Now, I would say that having an index solely on a low cardinality column is not necessarily a good thing, it is also not necessarily a bad thing either. Some systems can apply multiple indexes to filter a search or support a join and in those systems having several single column indexes, even on low cardinality columns, can improve performance of those searches and joins while also reducing storage consumption and improving insert, update, and delete performance as a bonus. IBM’s Informix, for example, can use multiple indexes for searches and also for data warehouse style queries in a Star or Snowflake schema designed database and such queries will perform better than the equivalent designs using compound key indexes instead.

(Asked to answer)

First: Never concatenate columns. It's a violation of First Normal Form and it well cause huge pains later.

Second: A PRIMARY KEY (or in fact any candidate key) does not have to be single column. If it takes three columns to reach uniqueness, then you define a key on those three columns combined. This is called a composite key.

Third: There are extremely few cases where the data does not have what I call a “business key”, the data used in the business to distinguish between elements in the group. Just ask people in the business why they use to identify an individual customer, product, contact, etc. and you will find a good primary key candidate.

Fourth: There are cases where it is appropriate to create a surrogate key (unique values in a single column generated by the computer). Having a composite PRIMARY KEY can be one of those reasons. But it's called surrogate for a reason: it should be added to the table in addition to the business key and both should be declared as keys to enforce their uniqueness. The surrogate keys can then be used in other tables to implement foreign key relationships; these values are preferably not used externally. Surrogate keys are typically used to improve join performance or save space but at the cost of needing more joins for some reports.

So in short: never concatenate columns; find the business key; add a surrogate key when needed but not to replace the business key.

Each table row has exactly one primary key. A primary key column is both unique and not null. This primary key is used to identify a row uniquely.

Other columns may be unique to prevent input errors. Also, the UNIQUE constraint creates an index in the background which may make queries faster.

Sample Table

CREATE PLAYER ( 
	ID INTEGER PRIMARY KEY, 
	FIRST VARCHAR(10), 
	LAST VARCHAR(100) NOT NULL, 
	JERSEY_NUMBER INTEGER UNIQUE NOT NULL 
); 

PRIMARK KEY Example

The SELECT below should return only one row because it matches the PRIMARY KEY column.

-- ID IS PRIMARY KEY 
SELECT * FROM PLAYER 
WHERE ID = 1; 

UNIQUE Example

The new player row is inserted into the database. The player’s jersey number cannot be the same as anyone else’s, and the UNIQUE constraint makes sure this is true.

INSERT INTO PLAYER 
	(ID, FIRST, LAST, JERSEY_NUMBER) 
	VALUES (2, 'Jane', 'Seymour', 12); 

Fair warning this is a rather long answer to a relatively short question.

This answer will be confined to the design of tables for relational databases. I am going to restate your question a bit in the hope that what I'm reading into it is the actual question you were asking. If it's not please tell me where it's not and I'll try to focus my answer differently.

I would restate your question as “Why should we use surrogate keys instead of natural keys when designing tables for database applications?”

Think of relational design as a stepwise process. An early part of that process is identifying the business entities that go into a specific data domain, the attributes naturally contained within each entity, and how we are going to uniquely identify each distinct set of values contained within a given entity(each such set of values is called a tuple). Notice that I have not used the terms table, column, or row? That is not simply being fussy, it is done to emphasize that we are dealing with a set of abstractions from business processes that we will be turning into database tables and their relations.

If we're being careful about this first level of design we will only identify attributes within each entity which are meaningful within the context of that entity. Simple examples would be things like personal name or family name in a person entity, company legal name or company doing business as name in a vendor entity, or main body color in an automobile inventory entity. We will keep identifying such attributes until we have captured all of the attributes that our business partners think of as part of a given entity, and then we may add additional attributes that we realize are an implicit part of the way the business people view this particular entity. One of the ways that we know we have captured enough information about a given entity is when the business partner is able to identify every tuple in that entity uniquely. Since we have only captured meaningful information about the entity in these attributes the set of attributes which uniquely identify the tuple must in fact be attributes which are meaningful in the business context.

Designers call these meaningful identifiers natural keys. If a single attribute uniquely identifies the tuple we call that a single part natural key, if it takes more than one we call that a multiple part or multi-part natural key. Whatever number of parts are needed, the set of attributes needed to identify that tuple constitute primary key of that entity. By definition this primary key is the natural key.

Everything we have done so far falls into the domain of something called logical data design. There are a large number of rules that help a database designer construct a robust design which follows best practices for relational database design. Once this logical design is built, reviewed, and validated it is time to turn to the the next step which is called physical design. The physical design will in turn be used to produce the actual code which will create the real database in whatever brand of relational database the client desires. There are a very large number of additional decisions needed in this step, but a crucial part of making the leap from logical to physical correctly is to ensure that all of the information captured in the logical model is correctly reflected into the physical.

For the specific answer to why use surrogate keys I'm not going to drive into the process of normalization or show the complexities that can occur when multipart natural keys are propagated across several generations of children in identifying relations. Suffice it to say that one additional driver towards surrogation is the size to which a grandchild or great-grandchild's natural key can grow.

This is where the process of surrogation may occur. Under some circumstances the designer may choose to replace a natural key with a simple numerical sequence, often just an integer that is incremented once for each row inserted in a given table. Such an integer still uniquely identifies each row, but it carries no information whatsoever about the actual values of the columns that were derived from logical attributes. That is where the term surrogate key comes from, it is a legitimate primary key which stands in for, or is a surrogate for, the natural key of that table.

Typical reasons for surrogation include replacing multiple long strings in a natural key with a much smaller and more easily manipulated single value, replacing the natural key with a value that is guaranteed to always be one step larger than any previous value, and replacing a natural key whose business processes may allow value changes over time with a value which will remain fixed for the entire lifespan of the given row. Such surrogate keys may also produce much more compact indexes, especially where the database software uses copies of the key as a way to enhance index usability. There is an entire additional discipline of physical design called dimensional database modeling which virtually demands surrogate keys for the tables it calls dimensions. Please be aware that surrogate keys do also have drawbacks.

I hope going into this level of detail has been helpful. If I get the chance I will expand this answer with a few web references that can go far more deeply into the specific pieces that I have summarized above.

UUID's are valuable because they are not sequential. In a distributed database, or even a normal relational database, generating incrementing numbers typically creates a LOCK, in that only one insert can generate a number at any one time. Various RDBMS's have mechanisms (preallocation) to get around this limitation, but then in most implementations you loose assumed the one-upness of the value.

With UUID's you can generate the UUID inside the database or within the application. Due to the quantity of available of UUID's you can safely assume that your generated UUID is unique, and quickly store it, and use it inside other records within the same transaction.

Lastly, i've found that optimizing UUID's at the database level involves storing them in in byte form instead of as strings. This means the UUID takes 16 bytes to store, instead of 32 (without hyphens) or 36 bytes (with hyphens). When these UUID's in byte form are indexed, the index is at least 50% smaller, which at high record counts, shaves some time off of a database fetch due to less B-Tree blocks being required to store the index, and a shorter tree height.

For an actual database table, given its placement and relationship(s) with other table(s) in the database, use of one or more columns for a primary key, to uniquely identify each row in the table, is absolutely essential. This uniqueness is one of the basic concepts of the relational database paradigm known as entity integrity.

The only occasion when I would not use primary keys is for data transfer, i.e. (1) when data is being either imported from a flat file into a database table, or (2) data is being exported from a database table into a flat file. In both cases the database table(s) used would have pre-defined primary key(s), but the flat files would not, even if they are temporarily resident in the database.

In addition to the schema-design-based relational DB reasons to have a PK…

You’d rarely not use a PK, particularly in MySQL InnoDB. If you don’t have a PK in InnoDB, it will auto-generate one for you and store it in the table and in any indexes based on the table. As this auto-generated PK is composed from all the column values in your row, it can take a lot of space in base table storage and in index storage.

Some situations where you may not need a PK:

• Work tables and temporary tables. These are often stored using the MEMORY storage engine and usually don’t have secondary indexes. You *can* have a PK on these, but you usually do because you’re actively using the work table PK for some purpose such as guaranteeing uniqueness, etc.
• Related to above, you are loading a CSV file from disk or doing some other big load or import, and you will build the final table using two or more load tables. You often load to intermediate tables that may be quite large, but that you’ll DROP after you’re done with the import. Note that is also the only situation where I may use the MyISAM engine in MySQL.

This is because all the RDBMS use a paging system to prevent it. This paging system was developed long before RDBMSs, because it was essential for the early computers that had very little memory. At its simplest, data is kept in blocks or pages on the disk and when the application asks for a block of data, it is returned a pointer to the block in memory. The application has a finite number of blocks and if the block is not in memory the least -recently-used (LRU) memory block is released, and the memory block is reallocated to read in the requested page of data on disk. As far as the application/database is concerned it has the resources to read the data, but under the covers, it is all being swapped around between disk and memory to cope. Naturally, it isn’t a free lunch. Other threads may be trying to do the same thing, and other threads lose all caching. Hogging the resources is always possible but is bad news in a multi-user system, just like eating all the pie in the canteen. You must only request just the data you actually need in any multi-user database, even when it doesn’t seem to max out the disk. It sure slows everything down.

UUIDs are great for creating globally unique identifiers that you can count on for basically zero collision [1]. That implies the following:

• You can create them in different databases with confidence that you can merge the table without collisions
• You can create anywhere in your app rather than relying on the database to create them (so you don't need to insert first and then retrieve the IDs)
• Because they are globally unique - you can consider them as truly immutable IDs for the records (usually by convention, since most databases don't provide write-once columns [2]).

They are a great candidate for unique keys if there are no natural keys for the data in question.

UUIDs are not without downsides, of course:

• 32-digit numbers are cumbersome to remember and use, especially if your customers are going to see them - for those records it's better to have an alternative ID format.
• not all databases support storing UUIDs in bytes so it can take up more space. This is in practice not a big deal since most databases have ample disk space, and UUIDs are seldom the main culprit for space consumption.

The biggest problem though is using UUIDs with clustered indexes. Clustered indexes orders the underlying physical table, so if a new UUID is inserted out of order, it will causes the table to be resorted, which can have big performance impacts for large tables [3]. If I recall correctly, MS SQL Server (and possibly MySQL) use clustered indexes for primary keys unless you specify otherwise.

My usual practice is to use int/bigint auto increment for primary keys, and a separate unique key with UUID, so that way I don't have to remember which database defaults to clustered primary keys.

---
1 - The quality of the generation depends on the random number generator - for v4 uuids - see Universally unique identifier.

2 - This can be enforced programmatically in systems that support triggers.

3 - Note this is not specific to UUIDs; any data type that doesn't get inserted sequentially will cause the same problem, so it's better to think of this as a property of clustered indexes instead - use them with care.

Actually, it depends on what you're doing with the table. In most situations, a fat primary key is a Bad Idea, especially in MySQL InnoDB, where the primary key is stored as the lookup key in secondary indexes.

A "wide" primary key (whether based on a single long column value or multiple columns) will make secondary indexes larger and more expensive, causing inefficient use of buffer pool, slower inserts and other operations, and general bad performance if you have a lot of secondary indexes.

That said, a carefully-chosen multi-column primary key *can* be a very good thing for specific types of use-cases. In InnoDB, the primary key effectively specifies the ordering and clustering in the B-Tree used to store the row. If you have a multi-column PK, the first column will be used as the major ordering, the second column as the secondary ordering within the major ordering, and so on.

An example of where this can be useful is if you have a set of User records with a largish set of properties that are loaded to your app every time a User logs in (a fairly common situation in online games, blog sites, etc). A table structured something like this

create table UserProperties ( 
UserID bigint, 
PropertyTypeID bigint, 
PropertyValue varchar(256), 
primary key (UserID, PropertyTypeID, PropertyValue) 
) engine=InnoDB; 

would work nicely, as all the properties of a given User will be clustered adjacently in InnoDB storage, guaranteeing that a User property load will happen quickly, and also likely guaranteeing that Property updates for an active User will be on pages loaded in the buffer pool.

If you need to do a lot of property type searches that span Users, this structure would be less good as you'll need an expensive secondary index, but other solutions may be useful as per your application requirements.

There seem to be different schools. Those with a database theory background often say that artificial IDs are evil, and only natural keys are allowed, because they represent reality. Many practitioners say that natural keys are evil, because they are rarely unique, are often more volatile than expected, and can be long and bulky, too.

For data warehouses, natural keys are a no-no, generated surrogate keys should be used, even for things like dates. Reason: the bulkiness has a high impact with the huge fact tables involved, and as the data are stored for very long, stability of the keys is even more of a requirement.