Re: [PATCHES] Foreign key type checking patch - Mailing list pgsql-hackers

On Tue, 2 Mar 2004, Fabien COELHO wrote:

>
> Hello again,
>
> I turn the discussion to the dev list as it seems more appropriate.
>
> So about the proposed patch to warn if foreign key type do not match the
> target key:
>
> > Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> > > I'm really not sure that it makes sense to warn for the fk cases where the
> > > semantics should be correct (if they're not we need to fix it or make it
> > > an error) but in which an error might have been made by the user because
> > > the types are different given that it at least seems reasonable to me that
> > > the fk type is allowable to be a subset of the referenced type.  I don't
> > > think simply different types is sufficient to be warning material.
> >
> > I can think of several cases where it might be reasonable for the types
> > to be different.  One case in particular that needs some thought is
> > where the FK and referenced PK are domains on a common base type.
>
>
> I'm looking forward to see an example where:
>
>   1) the difference in type is actually needed by the application.
>
>   2) a simple warning about the issue would be considered harmful.
>
>
> Let me describe some examples where IMVVHO a simple warning make sense,
> although they are silently accepted by postgres at the moment:
>
> 1/ integers
>
> CREATE TABLE foo(fid INT4 NOT NULL PRIMARY KEY, ...);
> CREATE TABLE bla(fid INT2 REFERENCES foo, ...);
>
> The application will be fine till you enter fid=32767, and
> it inserts will fail in bla with fid=32768. Much later on.

Which is fine if bla is meant to store a subset of the allowable foo
values.  It'd be really hard to say at bla creation time that there isn't
going to be a bla2 which say takes an int4 check (fid>=32768) which might
be being used for dividing up the foo space between multiple tables.

> 2/ chars
>
> CREATE TABLE foo(fid VARCHAR(4) NOT NULL PRIMARY KEY, ...);
> CREATE TABLE bla(fid VARCHAR(2) REFERENCES foo, ...);
>
> bla will be able to reference all 2-letters keys of foo, but no more.
> If you have some counter in foo, it will fail when it turns 3 letters.

Same as above.

> 3/ chars
>
> CREATE TABLE foo(fid VARCHAR(4) NOT NULL PRIMARY KEY, ...);
> CREATE TABLE bla(fid VARCHAR(8) REFERENCES foo, ...);
>
> declaring a larger size is not a problem here, however you will
> never be able to but any reference in bla larger than 4 as it must
> match its counter part in foo. So it is just a little bit stupid.

This one is fairly pointless for the single column case but a multiple
column match unspecified constraint could allow the full 8 characters if
there's a second column which is null.

> 4/ time
>
> CREATE TABLE day(quand DATE NOT NULL PRIMARY KEY, ...);
> CREATE TABLE event(quand TIMESTAMP REFERENCES day, ...);
>
> The intent could be that events should refer to some day already
> registered in the base. Obviously it does work, because the = will cast to
> timestamp, to only the 00:00:00 timestamp will match a day.

This one does seem fairly broken.

5/ domains

CREATE DOMAIN posint AS int4 check(value>0);
CREATE TABLE foo(fid int4 primary key);
CREATE TABLE bla(fid posint references foo);

The intent here is that foo may contain negative numbers but that those
rows won't be referenced by bla.  This is similar to 1 and 2.