Thread: Defining Keys or not?
10-29-04, 12:56 #1Registered User
- Join Date
- Aug 2004
Unanswered: Defining Keys or not?
We don't currently define keys for DB2 (OS390). We usually have a unique index, but in some cases we don't have keys defined or any indexes (small tables). No one I talk to seems to know why we don't define PKs or at least always Primary Indexes. But, in modeling the data to present to users, it's difficult to see what to show as the keys, and to describe what makes a row unique (answer I get: "I don't really know", this from the person creating the data).
So, what are the implications? Right now I'm guessing at the "uniquifyers", and showing keys on the model where there are no keys in the database.
10-30-04, 10:47 #2Registered User
- Join Date
- May 2003
If you want DB2 to make sure that rows are unique, then a unique index is needed. If you define a PK and a unique index does not already exist on the same columns, then DB2 will automatically create the unique index for you.
If you trust the applicaiton code that inserts, updates, or deletes the data, to enforce uniqueness, then that may be fine if they always do it correctly.
If the person who creates the database design (not necessarily the person who creates the table) does not know what makes a row unique, then you "may" have a design bust. However, not all tables need unique rows if a specific single row does not have to be retrieved after it has been inserted.M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390