Re: [HACKERS] 7.1beta4 bug creating a certain table

On Mon, 29 Jan 2001, Tom Lane wrote:

> Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> > CREATE TABLE pakolas_cikktetel (
> >     pakolas int4 not null,
> >     cikk int4 not null,
> >     minoseg int4 not null,
> >     sorszam int4 check (sorszam > 0),
> >     helyrol int4,
> >     helyre int4,
> >     mennyi numeric(14,4) not null ,
> >     lezarva bool default 'f',
> >     primary key (pakolas, cikk, minoseg, sorszam),
> >     unique (pakolas, cikk, minoseg, helyrol, helyre));
> 
> > CREATE TABLE keszlet_bevetel (
> >     keszletnovekedes int4 not null primary key,
> >     pakolas int4 not null,
> >     cikk int4 not null,
> >     minoseg int4 NOT NULL,
> >     foreign key (pakolas, cikk, minoseg)
> >     references pakolas_cikktetel(pakolas, cikk, minoseg));
> 
> > ERROR:  UNIQUE constraint matching given keys for referenced table 
> > "pakolas_cikktetel" not found
> 
> It appears to me that this is correct, since there is no constraint
> on the first column that says that those three columns form a unique
> key *by themselves*.  I believe there were bugs in the code that checked
> for this error before ...
> 
>             regards, tom lane
> 
I don't know the exact SQL definition whether my declaration is correct or
not. But, checking our model, we realized that our implementation is not
correct. So I should rewrite these definitions. Thanks! :-)

But, if this declaration is not correct in the sense of SQL standards, I
can imagine that PostgreSQL may allow such declarations. (A NOTICE may be
given instead of an ERROR.) Or, are there any drawbacks of allowing this?

Zoltan
--                         Kov\'acs, Zolt\'an                        kovacsz@pc10.radnoti-szeged.sulinet.hu
          http://www.math.u-szeged.hu/~kovzol                        ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz