Re: Query about foreign key details for php framework

Hey Adrian,

Yes, that is the problem of not being able to join on the table name, to
obtain these any fields from that table. Also, it is for a framework which
will be managing constrains/rules/adds/deletes etc. , so needs to know the
constrain details against each table.

On Tue, 1 Mar 2016 at 00:59 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:

> On 02/28/2016 03:42 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 columns that i need as a minimum, but as you know they are
> > all easy apart from the rules "on update" from the "RC" table.
>
> I am not following, update_rule is just a field in
> information_schema.referential_constraints, how is it any harder then
> delete_rule?
>
> The issue from what I understand is that
> information_schema.referential_constraints does not have a table_name
> field to constrain the information to a particular table. This leads
> back to the overriding question, what is the purpose of the query? I
> suspect it for use by the framework to set up attributes of a model
> based on a table, is that correct?
>
> >
> > I did start having a crack at the catalog tables but that is pretty
> > complicated.
> >
> > On Sun, 28 Feb 2016 at 01:21 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> > On 02/26/2016 08:29 PM, David Binney wrote:
> > > 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?
> > >
> >
> > Well it comes down to the question that was asked several times
> > upstream:
> >
> > what is the information you want to see?
> >
> > I am not talking about a query, but a description of what attributes
> you
> > want on what database objects.
> >
> > Also given, from previous post:
> >
> > "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."
> >
> > Is this not something that should be discussed with the framework
> > developers, or are we already doing that:)?
> >
> >
> >
> > --
> > 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