From: | Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>, PostgreSQL Developers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [PATCHES] Foreign key type checking patch |
Date: | 2004-03-02 08:45:55 |
Message-ID: | Pine.LNX.4.58.0403020920540.28778@sablons.cri.ensmp.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-patches |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2004-03-02 09:40:41 | Re: BTrees with record numbers |
Previous Message | Shridhar Daithankar | 2004-03-02 06:58:22 | Re: Avoid MVCC using exclusive lock possible? |
From | Date | Subject | |
---|---|---|---|
Next Message | Devrim GUNDUZ | 2004-03-02 13:21:20 | Turkish translation of FAQ (fwd) |
Previous Message | Andrew Dunstan | 2004-03-02 01:32:43 | Re: log_line_info |