Q
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).


This was last published in June 2002

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close