Re: Query about foreign key details for php framework

David,

try this;

SELECT tc.table_name AS name,
rc.constraint_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,
kcu2.table_name AS references_table,
kcu2.column_name as references_field,
kcu2.ordinal_position
FROM information_schema.referential_constraints rc
JOIN information_schema.table_constraints tc ON rc.constraint_name =
tc.constraint_name
JOIN information_schema.key_column_usage kcu ON kcu.constraint_name =
rc.constraint_name
JOIN information_schema.table_constraints tc2 ON rc.unique_constraint_name
= tc2.constraint_name
JOIN information_schema.key_column_usage kcu2 ON kcu2.constraint_name =
rc.unique_constraint_name
WHERE kcu.table_name = issues
AND rc.constraint_schema = 'public'
AND tc.constraint_type = 'FOREIGN KEY'
ORDER BY
rc.constraint_name,
kcu.ordinal_position;

On Fri, Feb 26, 2016 at 2:37 PM, David Binney <donkeysoft(at)gmail(dot)com> wrote:

> Its not really whats missing, but that query will pretty much return every
> foreign key in the database, rather than the ones for that particular table
> 'issues'. So in my case, instead of returning the 5 foreign keys for that
> table, it will return lots of rows < 1k. However, the mysql query will
> return just the 5 rows, this is the main reason i suspect the joins to not
> be correct, unless i am still missing something.
>
> I have added a sample below to show you what i mean. The first shows the
> 5fk's which can be seen in the table_constraints, but when i run the full
> query i will get (2622 rows) :
>
> testdb=# \d issues
> id | integer | not null
> default nextval('issues_id_seq'::regclass)
> issue_number | smallint |
> description_brief | character varying(2000) |
> description_full | text |
> active | smallint | default 1
> ordering | smallint | default 0
> issue_status_option_id | bigint | not null
> issue_priority_option_id | bigint | not null
> issue_complexity_option_id | bigint | not null
> issue_ux_effect_id | bigint | not null
> issue_stage_id | bigint | not null
> created | timestamp without time zone |
> modified | timestamp without time zone |
>
> testdb=# select * from information_schema.table_constraints where
> table_name = 'issues';
> testdb | public | issues_pkey | testdb
> | public | issues | PRIMARY KEY | NO | NO
> testdb | public | fk1 | testdb
> | public | issues | FOREIGN KEY | NO | NO
> testdb | public | fk2 | testdb
> | public | issues | FOREIGN KEY | NO | NO
> testdb | public | fk3 | testdb
> | public | issues | FOREIGN KEY | NO | NO
> testdb | public | fk4 | testdb
> | public | issues | FOREIGN KEY | NO | NO
> testdb | public | fk5 | testdb
> | public | issues | FOREIGN KEY | NO | NO
> testdb | public | 2200_77475_1_not_null | testdb
> | public | issues | CHECK | NO | NO
> testdb | public | 2200_77475_7_not_null | testdb
> | public | issues | CHECK | NO | NO
> testdb | public | 2200_77475_8_not_null | testdb
> | public | issues | CHECK | NO | NO
> testdb | public | 2200_77475_9_not_null | testdb
> | public | issues | CHECK | NO | NO
> testdb | public | 2200_77475_10_not_null | testdb
> | public | issues | CHECK | NO | NO
> testdb | public | 2200_77475_11_not_null | testdb
> | public | issues | CHECK | NO | NO
>
> On Fri, 26 Feb 2016 at 20:08 Stuart <sfbarbee(at)gmail(dot)com> wrote:
>
>> Off hand I don't see anything wrong with the query. You will probably
>> need to examine what specifically is missing in the results and check table
>> and constraint definitions.
>> On Feb 26, 2016 13:45, "David Binney" <donkeysoft(at)gmail(dot)com> wrote:
>>
>>> 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
>>>
>> --
> Cheers David Binney
>