Re: Multicolumn foreign keys need useless unique indices? - Mailing list pgsql-hackers

On Fri, 2002-09-13 at 16:00, Tom Lane wrote:
> Rod Taylor <rbt@rbt.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