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.
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@cri.ensmp.fr