> neither of these constraints make any sense (the PK itself assures uniqueness)

See the posts with the title "Unique foreign key for child tables." It's needed to be the target for a foreign key.

> UPDATEs used to be slow for non-selective indexes

This is what I'm trying to avoid. Does anyone know it was changed and what version? We won't finish the migration from FB 1.5 until year end.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Tuesday, October 16, 2012 4:00 AM
To: 'firebird-support@yahoogroups.com'
Subject: [firebird-support] RE: Order of key elements

>What is the effective difference between these two key elements orderings?
>
>ALTER TABLE T ADD CONSTRAINT U UNIQUE (PK, HIGH_MAX_DUP)
>
>ALTER TABLE T ADD CONSTRAINT U UNIQUE (HIGH_MAX_DUP, PK)

If PK is the primary key of T, then I'd say that a UNIQUE constraint is basically a 'relaxed version' of the primary key and that neither of these constraints make any sense (the PK itself assures uniqueness). As for having an index on these fields, the first doesn't make any sense since you already have the primary key, whereas the second would be useful only if the distribution of (the almost Boolean field) HIGH_MAX_DUP was extremely skewed (e.g. having a index on the field GENDER can be useful if you search for men with breast cancer since over 99% of breast cancers appear amongst women, the same index will be slowing down things if searching for women). The second index was also the typical way to write an index on relatively low-selective fields in Firebird in its infancy (around 10 years ago), UPDATEs used to be slow for non-selective indexes (I don't know when this changed, but have heard less about it the last few years, so I think it is less of an issue with recent Firebird versions).

HTH,
Set


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links