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 <[EMAIL PROTECTED]> 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 - [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
