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.
When using PostgreSQL 7.3, the error message would be:
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
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:
Now if we look at slave, the trigger is gone:
test=# drop trigger "RI_ConstraintTrigger_32656784" on slave;
And we can insert any value into slave.test=# \d slave Table "slave" Column | Type | Modifiers -----------+---------+----------- master_id | integer |
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.
Again, the output is rather wide. But we can see, we have two triggers which refer to
test=# SELECT oid, relname FROM pg_class WHERE relname = 'master';
oid | relname
32655865 | master
test=# select tgrelid, tgname, tgargs from pg_trigger where tgrelid = 32655865;
slavewhich need to be deleted.
If we have more than one foreign key, the process is a bit more complex. We will cover that in the next section.
test=# drop trigger "RI_ConstraintTrigger_32656794" on master;
test=# drop trigger "RI_ConstraintTrigger_32656796" on master;
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
The solution is to review the triggers for
slave2. First, we need to
know more about
test=# SELECT oid, relname FROM pg_class WHERE relname = 'slave2';
oid | relname
32656809 | slave2
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
and the other to
master_id2. Therefore, it is
RI_ConstraintTrigger_32656817 which we wish to remove using this
You must use double quotes on the constraint name. By default, object names are translated to lower case if they are not quoted.
test=# drop trigger "RI_ConstraintTrigger_32656817" on slave2;
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:
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.
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)
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.