Re: Difference between Primary Key and DDS Unique index?

Lennon_s_j 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.

The FOREIGN KEY constraint wants to reference a PRIMARY KEY constraint to enable the Referential Integrity rules. Unless the DDS PF had been modified with ALTER TABLE ADD PRIMARY KEY CONSTRAINT or had the constraint added by ADDPFCST TYPE(*PRIKEY), then iNav is correctly excluding the file from the list of available files against which the relationship could be established.

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.

That confirms the PRIMARY KEY CONSTRAINT did not already exist, and that after the key was added, the iNav properly displayed the now-eligible file in the list.

A DSPDBR showed no additional LF had been added so I assume
the primary key was using the unique index

Adding a PRIMARY KEY CONSTRAINT creates a constraint definition containing additional attributes designating the rules for enforcing unique values & any RI established against that key; also creating a unique keyed access path if one does not exist. The request to add the primary key constraint is not a request to create a logical file; i.e. a Primary Key constraint is not [manifest as] a logical file. Similarly a DSPDBR does not show [an LF for] the access path established for the K-spec defined in the DDS source of a PF. Both a constraint access path and an access path defined in the DDS source of a PF are examples of access paths owned by the PF, not an access path owned by any LF.

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

A "unique DDS index" is in fact different than a primary key. A unique keyed access path is merely one part of the implementation of a primary key constraint definition. The primary key creates a unique access path without any implementation object visible; i.e. no *FILE object, LF or otherwise. For the SQL interfaces, such as ODBC for iNav, the constraint is potentially no more than a row in the SQL Catalog; e.g. the catalog VIEW named SYSCST. On probably any other database, even the SQL INDEX is not visible, except as a row in the catalog. Unless there is a row in the catalog to represent the constraint, there is no constraint for iNav to list.

If a "unique index" [unique keyed access path] was defined in the DDS of the PF, then adding a primary key constraint will add the additional attributes specific to the constraint as part of the constraint definition along with a row into the catalog; both the original access path and the new constraint implemented using that access path, will be presented as the PRIMARY KEY constraint when shown in DSPFD [for both *ACCPTH and *CST information]. The removal of the constraint allows the option to leave the unique key [i.e. access path] associated with the database physical file or remove the unique key; effecting removal of the row from the catalog in either case. Without the additional attributes from the constraint definition, a remaining access path is no longer a constraint, and no longer eligible for RI enforcement; only the unique attribute [of the access path, not the primary key constraint uniqueness] remains enforced.

Regards, Chuck