AW: Difference between Primary Key and DDS Unique index?

Hi,

I think there are different definitions:
Primary Key Constraint, Unique Key Constraint, Unique Index

A Primary Key is a special kind of Unique Key Constraint that will be
integrated directly in the SQL Table or Physical file.
A NULL value is not allowed in the column that makes up the primary key.
A Primary Key Constraint can be used to build referential integrities, i.e.
define dependencies between Tables or physical files
There is only a single primary key constraint allowed for a physical file or
SQL Table.
A primary key constraint will be added with the CL Command ADDPFCST or with
the SQL Command CREATE/ALTER TABLE ... ADD Primary Key Constraint.

A unique key constraint is a unique key that will also be integrated
directly in the SQL Table or physical file.
For the same table several unique key constraints (with different key
fields) are allowed.
A unique key constraint can be implemented for columns/fields where NULL
values are allowed.
A unique key constraint will be added with the CL Command ADDPFCST or with
the SQL Command CREATE/ALETER TABLE.

A unique index (defined in an DDS described logical file or an SQL index) is
a separate database object that contains an access path.

As of data access:
Primary Key Constraints, Unique Key Constraints and unique indexes are
considered by both (CQE and SQE) query optimizers. AFAIK Key Constraints are
checked first and then the access paths located within the separate database
objects. CQE optimizer then searches through the access paths beginning with
the last created one. The SQE optimizer first sorts the access paths
according the join fields, where conditions and group by conditions
specified within the SQL Statement.
Neither an SQL Index nor a Key constraint can be specified within an SQL
statement.

Even though a DDS described keyed logical file can be specified within an
SQL Statement, it is recommended not to do.
If a DDS described logical file is specified within an SQL Statement, the
query will be executed by the CQE (classic query engine).
The CQE optimizer analyzes the DDS and takes the field selection select/omit
clauses and join information, but ignores the key information and rewrites
the query based on the physical files/tables with these information. After
rewriting the optimization process begins. At this time the optimizer do not
know anymore that there was specified a logical file with a key.

It is the query optimizer which decides which access paths to use or if any
other access method such as table scan is used.

Native I/O allows you to access a primary key constraint defined in an SQL
table as if it is defined with UNIQUE and Key fields within a DDS described
physical file.
An SQL index can be used within native I/O like any DDS described logical
file.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Lennon_s_j@xxxxxxxxxxx
Gesendet: Thursday, 28. January 2010 23:29
An: midrange-l@xxxxxxxxxxxx
Betreff: Difference between Primary Key and DDS Unique index?

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