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.
Some of this article came from Referential Integrity Tutorial & Hacking the Referential Integrity tables
on the PostgreSQL Technical Documentation website. Other
information came from DarcyB.
The sample tables
These are the sample tables we will use:
CREATE TABLE master( id int not null, primary key(id)); CREATE TABLE slave( master_id int); ALTER TABLE slave ADD FOREIGN KEY (master_id) REFERENCES master (id) ON DELETE CASCADE;
Here is the sample master data we will use:
test=# insert into master values (1); INSERT 32656802 1 test=# select * from master; id ---- 1 (1 row)
The sample test cases
And a simple test of the relational integrity. We should not be able to add anything to
slave which is not already in master.
test=# insert into slave values (1);
INSERT 32656803 1
test=# insert into slave values (2);
ERROR: <unnamed> referential integrity violation - key referenced from slave not found in master
When using PostgreSQL 7.3, the error message would be:
test=# insert into slave values (2);
ERROR: $1 referential integrity violation - key referenced from slave not found in master
Note that under 7.3, “$1” is actually the constraint name. We will make use of that
when deleting the constraint and we will also show how to give it a more appropriate
and non-default name.
Dropping a foreign key with PostgreSQL < 7.3
After creating the tables, the definitions look like this:
test=# \d master Table "master" Column | Type | Modifiers --------+---------+----------- id | integer | not null Primary key: master_pkey Triggers: RI_ConstraintTrigger_32655872, RI_ConstraintTrigger_32655874 test=# \d slave Table "slave" Column | Type | Modifiers -----------+---------+----------- master_id | integer | not null Triggers: RI_ConstraintTrigger_32655870 test=#
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:
test=# drop trigger "RI_ConstraintTrigger_32656784" on slave;
DROP
Now if we look at slave, the trigger is gone:
test=# \d slave Table "slave" Column | Type | Modifiers -----------+---------+----------- master_id | integer |
And we can insert any value into slave.
test=# insert into slave values (1234); INSERT 32656805 1 test=# select * from slave; master_id ----------- 1 1234
But as pointed out to me by Fernando Nasser, there is more to it than that. There
are two triggers on the master table which must also be dealt with.
test=# SELECT oid, relname FROM pg_class WHERE relname = 'master';
oid | relname
----------+---------
32655865 | master
(1 row)test=# select tgrelid, tgname, tgargs from pg_trigger where tgrelid = 32655865;
Again, the output is rather wide. But we
can see, we have two triggers which refer to slave
which need to be deleted.
test=# drop trigger "RI_ConstraintTrigger_32656794" on master;
DROP
test=# drop trigger "RI_ConstraintTrigger_32656796" on master;
DROP
If we have more than one foreign key, the process is a bit more complex. We will cover that
in the next section.
Finding the right trigger under PostgreSQL < 7.3
The previous example was pretty easy. Let’s go complex with these tables:
CREATE TABLE master2( id int not null, primary key(id)); CREATE TABLE slave2( master_id int, master_id2 int); ALTER TABLE slave2 ADD FOREIGN KEY (master_id) REFERENCES master (id) ON DELETE CASCADE; ALTER TABLE slave2 ADD FOREIGN KEY (master_id2) REFERENCES master2 (id) ON DELETE CASCADE;
For this exercise, we will remove the reference to table master2
.
The solution is to review the triggers for slave2
. First, we need to
know more about slave2
.
test=# SELECT oid, relname FROM pg_class WHERE relname = 'slave2';
oid | relname
----------+---------
32656809 | slave2
(1 row)
Now we have the OID of the master table. We can use that to find the triggers on that table.
test=# select tgrelid, tgname, tgargs from pg_trigger where tgrelid = 32656809;
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.
Dropping a foreign key with PostgreSQL >= 7.3
For 7.3, here are the resulting table definitions:
test=# \d master Table "public.master" Column | Type | Modifiers --------+---------+----------- id | integer | not null Indexes: master_pkey primary key btree (id) test=# \d slave Table "public.slave" Column | Type | Modifiers -----------+---------+----------- master_id | integer | not null Foreign Key constraints: $1 FOREIGN KEY (master_id) REFERENCES master(id) ON UPDATE NO ACTION ON DELETE CASCADE
NOTE: I have wrapped the output to make it better in the browser.
In this case, neither master nor slave has triggers. But they do have other useful things.
In this case, to drop the foreign key on slave, all we need to do is :
test=# alter table slave drop constraint "$1"; ALTER TABLE
Each foreign key will have a different name. Just use the correct name to drop it. You must
use double quotes on the constraint name. But I don’t know why.
Naming the foreign key under PostgreSQL >= 7.3
When creating the foreign key, you can name provide a more meaningful name using this syntax:
test=# ALTER TABLE slave2 add constraint abc
FOREIGN KEY (master_id2)
REFERENCES master2 (id) ON DELETE CASCADE;
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
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.