Re: Query about foreign key details for php framework

Those joins used in the source query, do not seem enough from my
observations. It is joining on schema + constraint name, which can match
multiple constraints as it is not unique. If i can work out how to join all
three tables correctly, that would probably be the answer. However, i don't
want to ignore the fact that there might be a better way to get those
values in the select from alternative tables. I just don't know enough
about the schema tables to be sure.

(select distinct * from information_schema.referential_constraints) rc
JOIN information_schema.key_column_usage kcu
ON kcu.constraint_name = rc.constraint_name
AND kcu.constraint_schema = rc.constraint_schema

On Fri, 26 Feb 2016 at 19:13 Stuart <sfbarbee(at)gmail(dot)com> wrote:

> David,
>
> Can you elaborate. Can you explain what you mean by "joins to those
> tables are incorrect" and "not joining correctly"? How are you determining
> this incorrectness?
>
> If you believe results are incorrect, what is incorrect/missing and why?
> The answer will be in how the tables and constraints are defined.
> On Feb 26, 2016 11:29, "David Binney" <donkeysoft(at)gmail(dot)com> wrote:
>
>> Hey Stuart,
>>
>> Well I should be more specific that it is not valid at all, because the
>> joins to those tables are incorrect. When joining between those three
>> tables it is not joining correctly, which means the data is potentially
>> invalid, however its close, looking if you know what I mean.
>>
>>
>>
>> On Fri, 26 Feb 2016 at 15:18 Stuart <sfbarbee(at)gmail(dot)com> wrote:
>>
>>> David,
>>>
>>> Just to toss my 2 cents in here, the initial question to your original
>>> post was, "what's broken with the postgresql version of select"? Your
>>> statement says it's partially working. What part is working and what part
>>> isn't working? Do you get errors from the postgresql select or does it just
>>> not give you all that you need?
>>>
>>> Stuart
>>> On Feb 26, 2016 04:38, "David Binney" <donkeysoft(at)gmail(dot)com> wrote:
>>>
>>>> Hey guys,
>>>>
>>>> I am having a tricky problem which I have not needed to solve before.
>>>> Basically one of the php frameworks I am using needs to get the same
>>>> dataset from mysql and postgres but I am not sure how to do the joins.
>>>>
>>>> Below i have the mysql version of the query which work ok, and after
>>>> that i have my attempt at the postgresql version, which is not joined
>>>> correctly. Any help would be greatly appreciated, and in the meantime i
>>>> will keep guessing which columns need to be joined for those three tables,
>>>> but I am thinking there could be a view or something to solve my problem
>>>> straight away??
>>>>
>>>> -------mysql working version----------
>>>> SELECT
>>>> *
>>>> FROM
>>>> information_schema.key_column_usage AS kcu
>>>> INNER JOIN information_schema.referential_constraints AS rc ON (
>>>> kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
>>>> AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
>>>> )
>>>> WHERE
>>>> kcu.TABLE_SCHEMA = 'timetable'
>>>> AND kcu.TABLE_NAME = 'issues'
>>>> AND rc.TABLE_NAME = 'issues'
>>>>
>>>> ---- postgresql partial working version--------------
>>>>
>>>> select
>>>> rc.constraint_name AS name,
>>>> tc.constraint_type AS type,
>>>> kcu.column_name,
>>>> rc.match_option AS match_type,
>>>> rc.update_rule AS on_update,
>>>> rc.delete_rule AS on_delete,
>>>> kcu.table_name AS references_table,
>>>> kcu.column_name AS references_field,
>>>> kcu.ordinal_position
>>>> FROM
>>>> (select distinct * from information_schema.referential_constraints) rc
>>>> JOIN information_schema.key_column_usage kcu
>>>> ON kcu.constraint_name = rc.constraint_name
>>>> AND kcu.constraint_schema = rc.constraint_schema
>>>> JOIN information_schema.table_constraints tc ON tc.constraint_name =
>>>> rc.constraint_name
>>>> AND tc.constraint_schema = rc.constraint_schema
>>>> AND tc.constraint_name = rc.constraint_name
>>>> AND tc.table_schema = rc.constraint_schema
>>>> WHERE
>>>> kcu.table_name = 'issues'
>>>> AND rc.constraint_schema = 'public'
>>>> AND tc.constraint_type = 'FOREIGN KEY'
>>>> ORDER BY
>>>> rc.constraint_name,
>>>> cu.ordinal_position;
>>>>
>>>> --
>>>> Cheers David Binney
>>>>
>>> --
>> Cheers David Binney
>>
> --
Cheers David Binney