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

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.