Re: Query about foreign key details for php framework

>
>
> On Sat, 27 Feb 2016 at 07:35 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
> wrote:
>
>> On 02/26/2016 10:47 AM, David Binney wrote:
>> > That is exactly the desired result, but in my db it is returning 2k rows
>> > with exactly the same query, even filtered to a specific table.
>>
>> Note to self, read the entire doc page:
>>
>> http://www.postgresql.org/docs/9.5/interactive/information-schema.html
>> "
>> Note: When querying the database for constraint information, it is
>> possible for a standard-compliant query that expects to return one row
>> to return several. This is because the SQL standard requires constraint
>> names to be unique within a schema, but PostgreSQL does not enforce this
>> restriction. PostgreSQL automatically-generated constraint names avoid
>> duplicates in the same schema, but users can specify such duplicate names.
>>
>> This problem can appear when querying information schema views such
>> as check_constraint_routine_usage, check_constraints,
>> domain_constraints, and referential_constraints. Some other views have
>> similar issues but contain the table name to help distinguish duplicate
>> rows, e.g., constraint_column_usage, constraint_table_usage,
>> table_constraints.
>>
>
​
On Fri, Feb 26, 2016 at 9:29 PM, David Binney <donkeysoft(at)gmail(dot)com> wrote:

> Hey adrian,
>
> You are correct that the distinct will chomp the resultset down to the
> correct count, I am just concerned that there will be cases where it might
> not be accurate between the "rc" and the "kcu" joins as there is no table
> reference. I have simplified the query right down to just the join that i
> am unsure about. You can see below that as soon as i add the rc.unique_constraint_name,
> the distinct is no longer returning one row. In this case its fine because
> the rc values are the same and would distinct away, but there might be a
> case where they are diferent and you would have two rows and not know which
> values are correct?
>

​David,

You are correct. Since referential_constraints lacks a table name you are
hosed for this particular query - unless you ensure that your database is
also standard compliant by not introducing duplicate constraint names
within the same schema. If you cannot do that then the only solution to
obtain a correct result is to use pg_catalog tables directly and bypass
information_schema altogether.

http://www.postgresql.org/docs/9.5/interactive/catalogs.html

David J.
​