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



DROP TRIGGER  --  remove a trigger


DROP TRIGGER name ON table



The name of an existing trigger.


The name of a table.



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.


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.


Destroy the if_dist_exists trigger on table films:

DROP TRIGGER if_dist_exists ON films;



There is no DROP TRIGGER statement in SQL92.


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



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;
spaceport=> DROP TRIGGER \"RI_ConstraintTrigger_59569\" ON groups;
spaceport=> DROP TRIGGER \"RI_ConstraintTrigger_59571\" ON groups;

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-2015 The PostgreSQL Global Development Group