Re: Query about foreign key details for php framework

Sorry meant to remove one of the unnecessary joins...

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,
kcu.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.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 4:22 PM, Stuart <sfbarbee(at)gmail(dot)com> wrote:

> 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
>>
>
>