Dunbar, Norman (Capgemini) wrote:

> The way I see it is this:
>
> * A unique index prevents me from entering two rows with the same data
> in all the column(s) making up the index;
>
> * A unique constraint is a rule to enforce a business rule or decision.
> The constraint will use a unique index to enforce the constraint.

Which is way over thinking the problem. When the core of the InterBase
ODS was designed in 1982, the need to enforce uniqueness was obvious,
but the place of SQL in the relational world was not, nor was there a
SQL standards committee, let alone a standard. Unique indexes are an
historical artifact.

Eventually the world discovered the inner beauty of SQL, begat a
standards committee, and in their second or third try at a standard,
the committee discovered constraints ... and many Unique Constraints
blossomed. The standard says that the referenced column in a
Foreign Key constraint must have a Unique or Primary Key constraint,
and being slaves to the standard that's what we do.

If you care to impute a semantic difference between the two - go for
it. Many religions have been based on less. Practically, they're
implemented the same way except for the DDL and an entry in
RDB$CONSTRAINTS.

> * You simply cannot have referential integrity between two tables unless
> you have a primary key or a unique constraint on the parent table.

Right. Because my standard tells me so...


Cheers,


Ann