Re: [PATCHES] Foreign key type checking patch

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(at)megazone(dot)bigpanda(dot)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.

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.

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.

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.

etc.

--
Fabien Coelho - coelho(at)cri(dot)ensmp(dot)fr