Structure vs Index

[nikosv]

When you set a PK in creating a table,a secondary index is created on the key.The table is heap with a secondary btree index by default.

When you don't set a PK at the table's creation time and instead modify
the table's structure to btree unique on the key afterwards, it acts like a PK constraint in not allowing duplicates.

However how is this enforced ? I searched through the catalogue like iiindexes,
iiconstraints etc and I can't find anything.Also, in that case when doing a select straight on the key does it use the structure to retrieve the rows? Does it mean that I don't need to manually assign an index on the table's key and can just rely on the structure?

[Roy Hann]

nikosv wrote:

> When you set a PK in creating a table,a secondary index is created on
> the key.The table is heap with a secondary btree index by default.

That is true, but for performance reasons you may want to have a unique
physical key using the same column(s) and then, knowing you have it, and
also for performance reasons, you might declare the PK using INDEX =
BASE TABLE STRUCTURE to avoid creating a redundant secondary index.

> When you don't set a PK at the table's creation time and instead modify
> the table's structure to btree unique on the key afterwards, it acts
> like a PK constraint in not allowing duplicates.

That is true, but it is only a coincidence that can arise when the
efficient physical key happens to also be your primary logical key.
Admittedly that will often be the case so it is easy to overlook the
fact that the physical and logical keys are different things doing
different jobs. The physical key is locating the storage space where a
row physically resides, and clustering associated rows on a page. The
primary key is uniquely distinguishing a fact so you can confidently
refer to it by value.

> However how is this enforced ? I searched through the catalogue like
> iiindexes, iiconstraints etc and I can't find anything.

The secondary index that gets created is physically organized as a
unique btree by default. When you insert a row with a duplicate primary
key you get a conflict on the secondary index. When you rely on a
unique physical key on the base table the same thing happens but on the
base table.

> Also, in that case when doing a select straight on the key does it use the structure
> to retrieve the rows? Does it mean that I don't need to manually assign
> an index on the table's key and can just rely on the structure?

Yes. But whether it is best to exploit the secondary index that
supports the constraint or to enforce the constraint using the base
table structure is a decision you might want to think about. It's not
irreversible so you can try both. These days it would have to be a
pretty extreme table or a pretty demanding application before it makes
enough difference to fret about it. I think I'd do whatever is easiest
and ignore what goes on under the covers until it proves it's a problem
worth spending time solving. (“Premature optimization is the root of
all evil”, to quote Knuth.)

Roy

[G Jones]

> However how is this enforced ? I searched through the catalogue like
> iiindexes, iiconstraints etc and I can't find anything.

See iikey_columns.

[nikosv]

Hi Roy,
thanks for replying. I'll take your points into consideration in trying to find the optimal solution.