Thread: 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.weaver@protolabs.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.weaver@protolabs.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@sss.pgh.pa.us> wrote:

>gary.weaver@protolabs.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 !=3D 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=B9re 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=B9t 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=B9re 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 s=
ole use of the intended recipient(s) and may contain confidential, propriet=
ary or privileged information. It should be used or disseminated for the pu=
rpose of conducting business with Proto Labs. It may contain Information go=
verned by U.S. Export Control laws and regulations. If export controlled in=
formation, it must not be transferred to a foreign person without the prope=
r authorization of the applicable U.S. Government organization. Any unautho=
rized review, use, disclosure, or distribution is prohibited. If you receiv=
ed 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.