The FreeBSD Diary

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.

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)

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

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.

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

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.

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;

test=# drop trigger "RI_ConstraintTrigger_32656794" on master;
DROP
test=# drop trigger "RI_ConstraintTrigger_32656796" on master;
DROP

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;

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

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.

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.

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.