- Is a primary key required, and if so, must it be specified that it is sorted ascending/descending?
- 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?
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
- Dozens more answers to tough DB2 questions from Craig Mullins are available.
- The Best IBM DB2 Web Links: tips, tutorials, scripts, and more.
- Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical DB2 questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in December 2002