Обсуждение: Help converting constraint triggers

I need some help understanding how to convert constraint triggers to
normal foreign keys.  I have several of them in my database that a
previous dba created.  Since they don't show up in most PostgreSQL
tools, they are becoming a real pain to deal with.

Here is an example of on of the constraint triggers:
CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
    AFTER INSERT OR UPDATE ON sys_user
    FROM sys_group
    NOT DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE "RI_FKey_check_ins" ('sys_user_group_id_fkey',
'sys_user', 'sys_group', 'UNSPECIFIED', 'group_id', 'group_id');


I believe this is the same as the foriegn key definition,

alter table sys_user
  add constraint sys_user_group_id_fkey foreign key(group_id)
  references sys_group(group_id)
  not deferrrable;

Is this correct?  If not, how would be proper foreign key be defined?

Thanks,

Chris

Chris Hoover <revoohc@gmail.com> writes:
> I need some help understanding how to convert constraint triggers to
> normal foreign keys.

You might try the contrib/adddepend script ...

            regards, tom lane

What version of pgadmin are you using?  I looked in pgadmin3 and it
does not show the constraint triggers.

On 8/3/05, Hemapriya <priyam_1121@yahoo.com> wrote:
>
> pgadmin will list all the trigger functions defined
> in each schema. it is a freeware.
>
> --- Chris Hoover <revoohc@gmail.com> wrote:
>
> > I need some help understanding how to convert
> > constraint triggers to
> > normal foreign keys.  I have several of them in my
> > database that a
> > previous dba created.  Since they don't show up in
> > most PostgreSQL
> > tools, they are becoming a real pain to deal with.
> >
> > Here is an example of on of the constraint triggers:
> > CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
> >     AFTER INSERT OR UPDATE ON sys_user
> >     FROM sys_group
> >     NOT DEFERRABLE INITIALLY IMMEDIATE
> >     FOR EACH ROW
> >     EXECUTE PROCEDURE "RI_FKey_check_ins"
> > ('sys_user_group_id_fkey',
> > 'sys_user', 'sys_group', 'UNSPECIFIED', 'group_id',
> > 'group_id');
> >
> >
> > I believe this is the same as the foriegn key
> > definition,
> >
> > alter table sys_user
> >   add constraint sys_user_group_id_fkey foreign
> > key(group_id)
> >   references sys_group(group_id)
> >   not deferrrable;
> >
> > Is this correct?  If not, how would be proper
> > foreign key be defined?
> >
> > Thanks,
> >
> > Chris
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map
> > settings
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
>

I tried running this an a test db, and it got some, but not all of the
constraint triggers.  Is there anything else to try?

Also, was my interpretation of the constraint trigger correct?  While
there are many constraint triggers defined, I don't think the volume
is large enough to make it prohibitive for me to just convert them by
hand.

Thanks,

Chris

On 8/3/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Hoover <revoohc@gmail.com> writes:
> > I need some help understanding how to convert constraint triggers to
> > normal foreign keys.
>
> You might try the contrib/adddepend script ...
>
>                         regards, tom lane
>

pgAdmin III v1.2.0..

--- Chris Hoover <revoohc@gmail.com> wrote:

> What version of pgadmin are you using?  I looked in
> pgadmin3 and it
> does not show the constraint triggers.
>
> On 8/3/05, Hemapriya <priyam_1121@yahoo.com> wrote:
> >
> > pgadmin will list all the trigger functions
> defined
> > in each schema. it is a freeware.
> >
> > --- Chris Hoover <revoohc@gmail.com> wrote:
> >
> > > I need some help understanding how to convert
> > > constraint triggers to
> > > normal foreign keys.  I have several of them in
> my
> > > database that a
> > > previous dba created.  Since they don't show up
> in
> > > most PostgreSQL
> > > tools, they are becoming a real pain to deal
> with.
> > >
> > > Here is an example of on of the constraint
> triggers:
> > > CREATE CONSTRAINT TRIGGER sys_user_group_id_fkey
> > >     AFTER INSERT OR UPDATE ON sys_user
> > >     FROM sys_group
> > >     NOT DEFERRABLE INITIALLY IMMEDIATE
> > >     FOR EACH ROW
> > >     EXECUTE PROCEDURE "RI_FKey_check_ins"
> > > ('sys_user_group_id_fkey',
> > > 'sys_user', 'sys_group', 'UNSPECIFIED',
> 'group_id',
> > > 'group_id');
> > >
> > >
> > > I believe this is the same as the foriegn key
> > > definition,
> > >
> > > alter table sys_user
> > >   add constraint sys_user_group_id_fkey foreign
> > > key(group_id)
> > >   references sys_group(group_id)
> > >   not deferrrable;
> > >
> > > Is this correct?  If not, how would be proper
> > > foreign key be defined?
> > >
> > > Thanks,
> > >
> > > Chris
> > >
> > > ---------------------------(end of
> > > broadcast)---------------------------
> > > TIP 5: don't forget to increase your free space
> map
> > > settings
> > >
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>


__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com