This page in other versions: 8.4 / 9.0 / 9.1 / 9.2 / 9.3  |  Development versions: devel  |  Unsupported versions: 7.1 / 7.2 / 7.3 / 7.4 / 8.0 / 8.1 / 8.2 / 8.3

DROP TRIGGER

Name

DROP TRIGGER  --  remove a trigger

Synopsis

DROP TRIGGER name ON table
  

Inputs

name

The name of an existing trigger.

table

The name of a table.

Outputs

DROP

The message returned if the trigger is successfully dropped.

ERROR: DropTrigger: there is no trigger name on relation "table"

This message occurs if the trigger specified does not exist.

Description

DROP TRIGGER will remove all references to an existing trigger definition. To execute this command the current user must be the owner of the table for which the trigger is defined.

Examples

Destroy the if_dist_exists trigger on table films:

DROP TRIGGER if_dist_exists ON films;

Compatibility

SQL92

There is no DROP TRIGGER statement in SQL92.

SQL99

The DROP TRIGGER statement in PostgreSQL is incompatible with SQL99. In SQL99, trigger names are not local to tables, so the command is simply DROP TRIGGER name.

See Also

CREATE TRIGGER

Comments


Aug. 7, 2003, 5:54 p.m.

Not sure if this is the place to put this, but here goes...
In PostgreSQL 7.2 (and probably earlier versions) there is no easy way of dropping unnamed (and possibly namded; haven\'t tried yet) foreign key constraints. To work around this you will need to use the DROP TRIGGER command, as outlined below:
1. Backup the database first. This procedure is somewhat messy.

2. Lets say we have a foreign key for the \'gid\' field of the \'offergroups\' table which references the \'gid\' field on the \'groups\' table. First, find the table with the foreign key constraint. Issue a \\d {tablename} command to find out what triggers the table has. There will be one \"RI_ConstraintTrigger\" on your table for each foriegn key constraint and two \"RI_CT\"s for each foreign key constraint on other tables which use your table for its primary key.

spaceport=> \\d offergroups
Table \"offergroups\"
Column | Type | Modifiers
-------------+---------+-----------
offerid | integer |
gid | integer |
primary_grp | boolean |
Triggers: RI_ConstraintTrigger_59567 <--This is a foreign key trigger

3. Look in the system table \"pg_trigger\" for this trigger (note: as far as I can tell, you cannot select the trigger individually with a where clause. You will need to scan the table manually).There will be two other triggers that will have the same value for the \"tgargs\" field, usually located directly after the trigger you are looking for. These are the corresponding triggers for the table that is the object of your foreign key constraints (in this case the table \"offergroups\" has a foreign key constraint based on the \"gid\" field in the table \"groups\").

spaceport=> SELECT tgrelid, tgname, tgargs FROM pg_trigger;
tgrelid | tgname | tgargs
---------+------------------------------+----------------------------------------------------------------------------------------------
...
59559 | RI_ConstraintTrigger_59567 | <unnamed>\\000offergroups\\000groups\\000UNSPECIFIED\\000gid\\000gid\\000
59553 | RI_ConstraintTrigger_59569 | <unnamed>\\000offergroups\\000groups\\000UNSPECIFIED\\000gid\\000gid\\000
59553 | RI_ConstraintTrigger_59571 | <unnamed>\\000offergroups\\000groups\\000UNSPECIFIED\\000gid\\000gid\\000
...

4. Drop these three triggers. Use the table that has the foreign key constraint as the table for the \'ON {tablename}\' part for the trigger that you looked up in step 3, then the table that is the object of the foreign key constraint for the other two triggers.

spaceport=> DROP TRIGGER \"RI_ConstraintTrigger_59567\" ON offergroups;
DROP
spaceport=> DROP TRIGGER \"RI_ConstraintTrigger_59569\" ON groups;
DROP
spaceport=> DROP TRIGGER \"RI_ConstraintTrigger_59571\" ON groups;
DROP

5. Your table is now free of one foreign key constraint. Pat yourself on the back for being so clever. :) Repeat the process for each other foreign key constraint you want to get rid of.


Feb. 15, 2005, 4:54 a.m.

I'm running the 7.3.4 version. I needed to determine how to delete foreign keys. I ran across this web page.

Subsequently, I found another web page that provided a little easier method than mentioned above. Just in case someone might need it I'm including it here.

alter table z1_ext_models drop constraint "$1" or in my case
alter table z1_ext_models drop constraint "$26" since I had made a mistake in a script I ran 26 times and I needed to delete 26 foreign key definitions.

The theory I read is that since the foreign key was added with ALTER TABLE you should also drop that constraint with ALTER TABLE. Sounds good to me.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group