PostgreSQL – removing foreign keys

I’m a big fan of PostgreSQL. I really like that database.
I’ve been working with client/server databases since 1989
using a wide variety of databases including Sybase, Oracle,
DB2, mySQL, Access, SQL Server, and SQL Anywhere. Everything has advantages and disadvantages but right
now, I prefer PostgreSQL.

Every relationship has sensitive points. With PostgreSQL, my pet peeve is dropping foreign keys. I’m
happy to say this peeve will disappear when I move the latest release (7.3). This
issue arose when I was working to add multiple watch lists to FreshPorts.
I wanted to rename a column, drop the FK, and add a new FK pointing to another table. I’m working with PostgreSQL
v 7.2.3 but this was 7.3, then I’d also be using the DROP COLUMN command which was included with that release. Regardless
of the version, I’d still need to drop a foreign key.

There are two triggers on master; one for the primary key, another for the not null constraint.
With slave, we have only one trigger, so it’s quite simple to find out which one to delete. To delete
that trigger, we issue this command:

The output is too wide for here. But
upon examination, you will see that one constraint refers to master_id
and the other to master_id2. Therefore, it is
RI_ConstraintTrigger_32656817 which we wish to remove using this
command:

test=# drop trigger "RI_ConstraintTrigger_32656817" on slave2;

You must
use double quotes on the constraint name. By default, object names are translated to lower case
if they are not quoted.

Don’t forget to delete the trigger on the master table as described at the end of the previous section.

When dropping that constraint name, you won’t have to use double quotes, because it is already lower case.
The best solution: always quote the constraint name.

This can be easier

The recent release of PostgreSQL 7.3 makes this easier. As you can see, the work is quite reduced when
it comes to removing a foreign key. There are many third party tools around which will do these jobs for you.
Using one of them may be a good idea.