Re: Difference between Primary Key and DDS Unique index?

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

Hmmm...  my memory of when I learned SQL terminology is a little fuzzy, 
but if I remember correctly...

In SQL, "Primary Key" is a key that uniquely identifies a record in a 
table.  SQL does not require the RDBMS to create an index over the 
field(s) that make up that primary key.  (But, most RDBMS software will 
create the index anyway, because it's just the most efficient way for 
the system to check if the keys are unique.)

So right now, DB2 for i will create a unique index when you declare a 
primary key.  However, it's not required to by SQL standards, so some 
day they could opt to use a different method of checking that your keys 
are unique (if they should ever find a better way, for example.)

Whereas a DDS unique index specifically states that there's an index on 
those fields.


On 1/28/2010 4:29 PM, Lennon_s_j@xxxxxxxxxxx wrote:
> I was experimenting with creating tables using iSeries Navigator.  (It
> has a pretty good interface.  Right click on Schema/new/table.)
>
> I wanted to add a foreign key constraint to an existing file defined
> with DDS and having a unique key.   iSeries Nav would not show the DDS
> file as being available in the drop down to use as a constraint.
>
> However, if I manually added a primary key constraint to the DDS file
> from the command line, the DDS file then became available to select for
> a foreign key in iSeries Nav.
>
> A DSPDBR showed no additional LF had been added so I assume the primary
> key was using the unique index, but it looks like a unique DDS index is
> somehow different from a primary key, at least from iSeries Nav’s point
> of view.  (V5R4 version of iSeries Nav.)