The FreeBSD Diary

The FreeBSD Diary (TM)

Providing practical examples since 1998

If you buy from Amazon USA, please support us by using this link.
[ HOME | TOPICS | INDEX | WEB RESOURCES | BOOKS | CONTRIBUTE | SEARCH | FEEDBACK | FAQ | FORUMS ]

Things look quiet here. But I've been doing a lot of blogging at dan.langille.org because I prefer WordPress now. Not all my posts there are FreeBSD related. I am in the midst of migrating The FreeBSD Diary over to WordPress (and you can read about that here). Once the migration is completed, I'll move the FreeBSD posts into the new FreeBSD Diary website.

PostgreSQL - removing foreign keys 6 December 2002
Need more help on this topic? Click here
This article has no comments
Show me similar articles

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.


Share
Need more help on this topic? Click here
This article has no comments
Show me similar articles