Re: [PATCHES] Foreign key type checking patch

On Tue, 2 Mar 2004, Fabien COELHO wrote:

>
> Hello Stephan,
>
> > > 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.
> > [...]
>
> Sure. This is NOT my point. I totally agree with you that the above
> example MAY BE what the user intends, and that it must be allowed.
> However it may ALSO be a bug that will pop up later on.
>
> Although it is POSSIBLE that this is fine, it is much more PROBABLE that
> it is a bug, hence I just suggest to issue a mere simple basic plain
> user-friendly little warning, what is quite different from issuing an
> error.
>
> Thus, the user has the information. He may chose to go on as that is what
> was meant, or maybe check the stuff and correct it.
>
> In postgres compilation, gcc uses the -Wall option to issue warnings about
> correct C constructs that may hide application bugs. This is the
> philosophy I'm suggesting here for this very small feature.
> "Dear user, what you ask is right, however it looks a little bit strange,
> so I tell you just in case." I'm sure you're pretty happy that the gcc
> developers put such features for basic programmers, and that you use
> them;-) Why not allowing that kind of approach in postgres?

Because producing noise warnings often *lower* the amount of use you get
from real warnings. If one has to wade through useless messages to divine
the ones that are meaningful, overall many people just start ignoring all
warnings. I could be convinced that it is "notice" material, since people
who don't want to see it probably don't want to see the other notices
either, but warning seems way to strong to me.

Fundamentally, I don't see a huge difference between this and
select * from foo,bla where foo.fid=bla.fid;
where the same general constraints on meaningful values apply.

> > > 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
>
> Isn't it what I'm saying?
>
> > 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.
>
> I do not understand. I can't see how you can put 8 characters in a
> reference which must match a 4 characters string.

Because in match unspecified, any column being null means the remainder of
the columns are not checked against the other table. IE given a key of
(fid, other), ('abcdefg', null) is valid in match unspecified even if the
other key can only have 4 characters.