Re: How do I drop a Complicated Constraint Trigger After Delete Execute Procedure?

On Wednesday 10 February 2010 7:07:08 pm Wang, Mary Y wrote:
> Ok. I typed the correct name this time, and got the same error.
> "drop trigger bug_assigned_to_fk on users;
> ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation
> users " "drop trigger bug_assigned_to_fk on bug;
> ERROR: DropTrigger: there is no trigger bug_assigned_to_fk on relation
> bug" Here is my user table:
> \d users
> Table "users"
> Attribute | Type | Modifier
>
> ----------------------+-----------------------+----------------------------
>------- -------------
> user_id | integer | not null default
> nextval('users_pk _seq'::text)
> user_name | text | not null default ''
> email | text | not null default ''
> user_pw | character varying(32) | not null default ''
> realname | character varying(32) | not null default ''
> status | character(1) | not null default 'A'
> shell | character varying(20) | not null default
> '/bin/bash' unix_pw | character varying(40) | not null default
> '' unix_status | character(1) | not null default 'N'
> unix_uid | integer | not null default '0'
> unix_box | character varying(10) | not null default 'shell1'
> add_date | integer | not null default '0'
> confirm_hash | character varying(32) |
> mail_siteupdates | integer | not null default '0'
> mail_va | integer | not null default '0'
> authorized_keys | text |
> email_new | text |
> people_view_skills | integer | not null default '0'
> people_resume | text | not null default ''
> timezone | character varying(64) | default 'GMT'
> language | integer | not null default '1'
> third_party | integer | not null default 1
> personal_status | character(32) |
> bemsid | integer |
> sensitive_info | character(64) |
> reason_access | text |
> organization | text |
> brass_first_time | character(1) | default '0'
> mail_sitenews_update | integer | default '0'
> doclinks_sort_order | character(1) | default 'A'
> Indices: idx_users_username,
> user_user,
> users_user_pw
>
> Someone mentioned about using 'alter table'. Would like would work? But
> I'm not sure how to do it because \d doesn't show the constraint.
>
> Any ideas?
> Mary
>

For what it is worth the manuals for this version are here:
http://www.postgresql.org/docs/manuals/archive.html

I do not see anything in the manual that shows ALTER TABLE being useful in this
situation. I am afraid 7.1 is before my time and at this point I cannot think
of a solution other than set the assigned_to value in bugs to NULL where
assigned_to=user_id. Sort of negates the point of a relationship between bugs
and users. Another option would be to create a 'dummy' user to
whom 'unassigned ' bugs would be referenced.

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