Problem solve Get help with specific problems with your technologies, process and projects.

Maximum number of columns in a primary key

I have a question regarding primary keys. How many max columns of primary keys can I have on a table? I know that DB2 does not allow more than 255 columns of table, in that case can I have all 255 columns as part of my primary key?

IBM's online SQL Reference manual, specifically the CREATE TABLE statement, says that a table can have up to 500 or up to 1012 columns (depending on the byte size of each row). For a primary key,

"The number of identified columns must not exceed 16 and the sum of their stored lengths must not exceed 1024. The length of any individual column must not exceed 255 bytes."

Perhaps that's where you got the 255 number from? In any case, 16 seems like a very workable limit on the number of primary key columns. If you have a table design that requires more, then I would take another look at it. Remember, the purpose of the primary key is to ensure entity integrity (i.e. uniqueness of rows), so if you need more than 16 columns to do that, perhaps you should look into using a surrogate key. See my previous answer Using generated numbers for primary keys (10 June 2002).

Dig Deeper on Oracle and SQL