Re: Query about foreign key details for php framework

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?

select distinct rc.constraint_name AS name,
rc.unique_constraint_name, ---added this to show that the matchup could
potentially be wrong
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 information_schema.referential_constraints rc,
information_schema.key_column_usage kcu
where kcu.constraint_name = rc.constraint_name
AND kcu.constraint_schema = rc.constraint_schema
and kcu.table_name = 'issues'
and kcu.constraint_name = 'fk5';
name | unique_constraint_name | column_name | match_type | on_update |
on_delete | references_table | references_field | ordinal_position
------+------------------------+----------------+------------+-----------+-----------+------------------+------------------+------------------
fk5 | issue_stages_pkey | issue_stage_id | NONE | NO ACTION |
CASCADE | issues | issue_stage_id | 1
fk5 | directorates_pkey | issue_stage_id | NONE | NO ACTION |
CASCADE | issues | issue_stage_id | 1

PS: thanks for persisting, I know this must be painful ;).

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.
> "
>
>
> Best guess it is this line:
>
> tc.constraint_name = rc.constraint_name
>
> If you look at the output from my query you will see that is has two
> entries for name = con_fkey. There is actually only one such FK on that
> table, but another of the same name on another table. As written now it
> will find that constraint_name across all tables.
>
> Rewriting see ^^^^^ in line:
>
> production=# 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
> AND tc.table_name = kcu.table_name
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> WHERE
> kcu.table_name = 'projection'
> AND rc.constraint_schema = 'public'
> AND tc.constraint_type = 'FOREIGN KEY'
> ORDER BY
> rc.constraint_name,
> kcu.ordinal_position;
>
> name | type | column_name | match_type | on_update |
> on_delete | references_table | references_field | ordinal_position
>
> ----------+-------------+-------------+------------+-----------+-----------+------------------+------------------+------------------
> con_fkey | FOREIGN KEY | c_id | NONE | CASCADE |
> CASCADE | projection | c_id | 1
> pno_fkey | FOREIGN KEY | p_item_no | NONE | CASCADE |
> CASCADE | projection | p_item_no | 1
>
>
> Going back to your MySQL query I came up with this:
>
> production=# SELECT
> distinct *
> 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)
> --join
> -- information_schema.tables
> --ON
> -- (tables.table_name = kcu.table_name AND tables.TABLE_SCHEMA =
> kcu.TABLE_SCHEMA)
> WHERE
> kcu.TABLE_SCHEMA = 'public'
> AND
> kcu.TABLE_NAME = 'projection';
>
> -[ RECORD 1 ]-----------------+---------------
> constraint_catalog | production
> constraint_schema | public
> constraint_name | pno_fkey
> table_catalog | production
> table_schema | public
> table_name | projection
> column_name | p_item_no
> ordinal_position | 1
> position_in_unique_constraint | 1
> constraint_catalog | production
> constraint_schema | public
> constraint_name | pno_fkey
> unique_constraint_catalog | production
> unique_constraint_schema | public
> unique_constraint_name | p_no_pkey
> match_option | NONE
> update_rule | CASCADE
> delete_rule | CASCADE
> -[ RECORD 2 ]-----------------+---------------
> constraint_catalog | production
> constraint_schema | public
> constraint_name | con_fkey
> table_catalog | production
> table_schema | public
> table_name | projection
> column_name | c_id
> ordinal_position | 1
> position_in_unique_constraint | 1
> constraint_catalog | production
> constraint_schema | public
> constraint_name | con_fkey
> unique_constraint_catalog | production
> unique_constraint_schema | public
> unique_constraint_name | container_pkey
> match_option | NONE
> update_rule | CASCADE
> delete_rule | CASCADE
>
>
>
>
> >
> > On Sat, 27 Feb 2016 at 01:16 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > On 02/25/2016 07:19 PM, David Binney wrote:
> > > Ah sorry adrian,
> > >
> > > I am a little in the dark as well since this is just a broken
> > piece of
> > > ORM i am attempting to fix, in the framework. So, maybe if you
> could
> > > help to reproduce that select list as a start that would be
> > great. But,
> > > I am suspecting they were trying to pull similar datasets from
> > mysql or
> > > postgres as an end goal.
> > >
> >
> > Alright I ran the Postgres query you provided and it threw an error:
> >
> > ERROR: missing FROM-clause entry for table "cu"
> > LINE 26: cu.ordinal_position;
> >
> > in the ORDER BY clause. Changing cu.ordinal_position to
> > kcu.ordinal_position obtained a result when run for a table in one
> of my
> > databases:
> >
> > production=# 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 = 'projection'
> > AND rc.constraint_schema = 'public'
> > AND tc.constraint_type = 'FOREIGN KEY'
> > ORDER BY
> > rc.constraint_name,
> > kcu.ordinal_position;
> >
> > -[ RECORD 1 ]----+------------
> > name | con_fkey
> > type | FOREIGN KEY
> > column_name | c_id
> > match_type | NONE
> > on_update | CASCADE
> > on_delete | CASCADE
> > references_table | projection
> > references_field | c_id
> > ordinal_position | 1
> > -[ RECORD 2 ]----+------------
> > name | con_fkey
> > type | FOREIGN KEY
> > column_name | c_id
> > match_type | NONE
> > on_update | CASCADE
> > on_delete | CASCADE
> > references_table | projection
> > references_field | c_id
> > ordinal_position | 1
> > -[ RECORD 3 ]----+------------
> > name | pno_fkey
> > type | FOREIGN KEY
> > column_name | p_item_no
> > match_type | NONE
> > on_update | CASCADE
> > on_delete | CASCADE
> > references_table | projection
> > references_field | p_item_no
> >
> > If this is not the desired result, then we will need more
> information.
> >
> > --
> > 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
>
--
Cheers David Binney