Re: BUG #11554: Enforce that number and type of both columns involved in fkey are same type

The following bug has been logged on the website:

Bug reference: 11554
Logged by: Gary Weaver
Email address: gary(dot)weaver(at)protolabs(dot)com
PostgreSQL version: 9.3.2
Operating system: Linux
Description:

To help adhere to the statement, "Of course, the number and type of the
constrained columns need to match the number and type of the referenced
columns." as noted in
http://www.postgresql.org/docs/9.3/static/ddl-constraints.html shouldn't PG
enforce that both columns involved in a foreign key constraint are of the
same number and type (e.g. int != bigint) at time of constraint creation?

And possibly the default for enforcement could be configured to be off, if
this were released in a 9.x release and then defaulted to on for 10.x+
releases of PG, if this is considered desirable.

The problem is that if you are forced to use a tool that syncs data between
databases of different types, other databases sometimes enforce that the
types cannot be different. While this is not a frequent use case, it just
makes sense in most cases that you'd want the two column types/sizes to be
the same.

Thanks for your help!

gary(dot)weaver(at)protolabs(dot)com writes:
> To help adhere to the statement, "Of course, the number and type of the
> constrained columns need to match the number and type of the referenced
> columns." as noted in
> http://www.postgresql.org/docs/9.3/static/ddl-constraints.html shouldn't PG
> enforce that both columns involved in a foreign key constraint are of the
> same number and type (e.g. int != bigint) at time of constraint creation?

No. SQL92 did say that, but in SQL99 and later, the requirement is only
that the column types be comparable. Which is what PG enforces.

> The problem is that if you are forced to use a tool that syncs data between
> databases of different types, other databases sometimes enforce that the
> types cannot be different. While this is not a frequent use case, it just
> makes sense in most cases that you'd want the two column types/sizes to be
> the same.

It would be more productive to lobby those other systems to become more
standards-compliant than to ask us to become less so.

In any case, this hardly seems like it would be the dominant problem
if you're trying to make schemas be exactly the same across multiple
products :-(.

regards, tom lane

Tom,

On 10/2/14, 6:58 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

>gary(dot)weaver(at)protolabs(dot)com writes:
>> To help adhere to the statement, "Of course, the number and type of the
>> constrained columns need to match the number and type of the referenced
>> columns." as noted in
>> http://www.postgresql.org/docs/9.3/static/ddl-constraints.html
>>shouldn't PG
>> enforce that both columns involved in a foreign key constraint are of
>>the
>> same number and type (e.g. int != bigint) at time of constraint
>>creation?
>
>No. SQL92 did say that, but in SQL99 and later, the requirement is only
>that the column types be comparable. Which is what PG enforces.

Ah, ok. Thanks for the clarification!

>
>> The problem is that if you are forced to use a tool that syncs data
>>between
>> databases of different types, other databases sometimes enforce that the
>> types cannot be different. While this is not a frequent use case, it
>>just
>> makes sense in most cases that you'd want the two column types/sizes to
>>be
>> the same.
>
>It would be more productive to lobby those other systems to become more
>standards-compliant than to ask us to become less so.

Understand, but I don't want to try swimming upstream. :)

>
>In any case, this hardly seems like it would be the dominant problem
>if you're trying to make schemas be exactly the same across multiple
>products :-(.

Completely agree. We¹re in a transitional state for a while where using a
sync was chosen as the option for the short-term, and the sync tool
complains. It was easy enough to fix, but it would have been nice if there
were an option to tell PG to warn us when the fkey column number/type
doesn¹t match the id number/type, since currently we have no need for
those to be different.

*However*, this would slow down PG a little to have to make that check, so
since you¹re saying its behavior is consistent with the standard, I
withdraw that request.

Thanks for the info, and thanks for all of your work on PG!

Confidentiality Notice: This email, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential, proprietary or privileged information. It should be used or disseminated for the purpose of conducting business with Proto Labs. It may contain Information governed by U.S. Export Control laws and regulations. If export controlled information, it must not be transferred to a foreign person without the proper authorization of the applicable U.S. Government organization. Any unauthorized review, use, disclosure, or distribution is prohibited. If you received this email and are not the intended recipient, please inform the sender by email and destroy all copies of the original message. Thank you for your cooperation.