Re: Multicolumn foreign keys need useless unique indices?

On Fri, 2002-09-13 at 16:00, Tom Lane wrote:
> Rod Taylor <rbt(at)rbt(dot)ca> writes:
> > On Fri, 2002-09-13 at 04:27, Christopher Kings-Lynne wrote:
> >> Hmmm - thinking about it, I don't see why postgres would need the entire
> >> thing to be unique...can't think of a reason at the moment. Stephen?
>
> > If it's not all unique, you cannot be guaranteed there is a single row
> > with those values in the referenced table.
>
> Right. The single-column unique constraint guarantees at most one
> match, but it isn't helpful for checking if there's at least one match.

Due to postgres's implementation we can't do the 'at least' part using
only index anyway - we must check the actual table.

> The spec obviously intends that the index supporting the unique
> constraint be useful for verifying the existence of a match.

Does the spec say _anything_ about implementing unique contraint using
an unique index ?

> I read this in SQL92:
>
> a) If the <referenced table and columns> specifies a <reference
> column list>, then the set of column names of that <refer-
> ence column list> shall be equal to the set of column names
> in the unique columns of a unique constraint of the refer-
> enced table.
>
> It says "equal to", not "superset of". So we are behaving per spec.

But we are doing it in a suboptimal way.

If we have unique index on t.i and we define additional unique
constraint on (t.i, t.j), then we don't need the extra unique index to
be created - the index on t.i is enough to quarantee the uniqueness of
(t.i,t.j) or any set of columns that includes t.i.

---------------
Hannu

PS. IMHO our unique is still broken as shown by the following:

hannu=# create table t(i int unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index 't_i_key' for
table 't'
CREATE TABLE
hannu=# insert into t values(1);
INSERT 41555 1
hannu=# insert into t values(2);
INSERT 41556 1
hannu=# update t set i=i-1;
UPDATE 2
hannu=# update t set i=i+1;
ERROR: Cannot insert a duplicate key into unique index t_i_key
hannu=#

DB2 has no problems doing it:

db2 => create table t(i int not null unique)
DB20000I The SQL command completed successfully.
db2 => insert into t values(1)
DB20000I The SQL command completed successfully.
db2 => insert into t values(2)
DB20000I The SQL command completed successfully.
db2 => update t set i=i+1
DB20000I The SQL command completed successfully.
db2 => update t set i=i-1
DB20000I The SQL command completed successfully.

neither has Oracle

SQL> create table t(i int not null unique);
Table created.
SQL> insert into t values(1);
1 row created.
SQL> insert into t values(2);
1 row created.
SQL> update t set i=i+1;
2 rows updated.
SQL> update t set i=i-1;
2 rows updated.
SQL>

----------------
Hannu