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

Primary keys in DB2

Our group was debating the pros and cons of DB2 versus Unisys RDMS while trying to design some new tables. Our debate became a little heated (as tech conversations do sometimes). Could you resolve a few questions regarding the physical implementation of DB2 tables for us please?
  1. Is a primary key required, and if so, must it be specified that it is sorted ascending/descending?
  2. If a primary key is defined, are the keys themselves physically stored separately (in a different table) from their data rows? Are the data rows physically stored in the primary key sequence?
Thank you very much for your time. Hopefully our group won't have to get out the boxing gloves.

OK, I can definitely help, but don't put away those boxing gloves just yet! I doubt these answers will be able to 100% settle any technology dispute.

First of all, DB2 does not require a primary key to be defined for each table. That being said, it is a good idea to define a primary key for every table. Without one, duplicate rows can exist and they can be very problematic to manage. Every primary key, though, requires a unique index to be defined for that key. The index data is maintained automatically and will be stored in a different underlying data set.

Secondly, the primary key will be composed of a column (or group of columns) in the table. Therefore, these columns exist in the same table as the rest of the data. The rows will not be sequenced by the primary key column(s) unless you specifically request them to be by making the primary key index a clustering index.

I'm intrigued by these questions... I wonder what all the fighting was about?

For More Information

Dig Deeper on IBM DB2 management