I was just annoyed to find out that a foreign key doesn't check whether
the referenced column has a sufficiently similar data type, it only checks
whether an = operator exists.  This masks schema design errors and typos.
Should this be tightened up, for example using the castability
characteristics between the two data types?

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 1 Dec 2003 13:04:49 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Foreign key data type mismatches
Peter Eisentraut wrote:

>I was just annoyed to find out that a foreign key doesn't check whether
>the referenced column has a sufficiently similar data type, it only checks
>whether an = operator exists.  This masks schema design errors and typos.
>Should this be tightened up, for example using the castability
>characteristics between the two data types?
>  
>

Sounds like a good idea. IMHO normally only *equal* data types should be 
referenced in a good data model design, but there's probably the need 
for some relaxed check option too.

Regards,
Andreas



Mon, 01 Dec 2003 13:40:37 +0100
Andreas Pflug <pgadmin@pse-consulting.de>
Re: Foreign key data type mismatches
Andreas Pflug wrote:
> Peter Eisentraut wrote:
> 
> >I was just annoyed to find out that a foreign key doesn't check whether
> >the referenced column has a sufficiently similar data type, it only checks
> >whether an = operator exists.  This masks schema design errors and typos.
> >Should this be tightened up, for example using the castability
> >characteristics between the two data types?
> >  
> >
> 
> Sounds like a good idea. IMHO normally only *equal* data types should be 
> referenced in a good data model design, but there's probably the need 
> for some relaxed check option too.

Yes, we already talked about throwing a warning if the primary/foreign
key data types are mismatched.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Mon, 1 Dec 2003 09:47:38 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Foreign key data type mismatches
On Mon, 1 Dec 2003, Peter Eisentraut wrote:

> I was just annoyed to find out that a foreign key doesn't check whether
> the referenced column has a sufficiently similar data type, it only checks
> whether an = operator exists.  This masks schema design errors and typos.
> Should this be tightened up, for example using the castability
> characteristics between the two data types?

Maybe, but IIRC the spec only requires comparability between the types
involved. Since we don't use the same rules as the spec for that,
existance of equality comparison was treated as the closest match to the
requirement at the time.

Mon, 1 Dec 2003 07:33:24 -0800 (PST)
Stephan Szabo <sszabo@megazone.bigpanda.com>
Re: Foreign key data type mismatches