Re: Query about foreign key details for php framework

On 02/25/2016 04:56 PM, David Binney wrote:
> Hey Adrian,
>
> 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
>
> Those are the needed columns, as the end resultset.

But that is different then what you are asking from the MySQL query:

http://dev.mysql.com/doc/refman/5.7/en/information-schema.html

There is no constraint_type in the information_schema tables you
reference in the MySQL query, unless I am missing something.

>
> On Fri, 26 Feb 2016 at 10:51 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 02/25/2016 04:38 PM, David Binney 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??
>
> The * in your MySQL query hides what it is you are trying retrieve.
>
> So what information are you after?
>
> Or to put it another way, what fails in the Postgres version?
>
> >
> > -------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
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
> --
> Cheers David Binney

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com