SEO

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

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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
>

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match