FK creation -- "ON DELETE NO ACTION" seems to be a no-op

From: "Webb Sprague" <webb(dot)sprague(at)gmail(dot)com>
To:
Cc: "General PostgreSQL List" <pgsql-general(at)postgresql(dot)org>
Subject: FK creation -- "ON DELETE NO ACTION" seems to be a no-op
Date: 2007-12-21 19:22:09
Message-ID: b11ea23c0712211122j3162f0d9s756928a0681dcd64@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi list,

First, my select version() gives:

PostgreSQL 8.2.4 on x86_64-pc-linux-gnu, compiled by GCC
x86_64-pc-linux-gnu-gcc (GCC) 4.1.2 (Gentoo 4.1.2 p1.0.2)

The Problem: when I run

ALTER TABLE currentsessions ADD CONSTRAINT
currentsessions_username_fkey FOREIGN KEY (username) REFERENCES
authorizedusers on delete no action on update cascade;

It is not updating the table correctly to reflect the "no action".
After \d currentsessions (and some editing) I get the following,
which, as I understand it, should say something about the "ON DELETE
NO ACTION":

... "currentsessions_username_fkey" FOREIGN KEY (username)
REFERENCES authorizedusers(username) ON UPDATE CASCADE

However, if I drop the constraint and rebuild it with

ALTER TABLE currentsessions ADD CONSTRAINT
currentsessions_username_fkey FOREIGN KEY (username) REFERENCES
authorizedusers on delete cascade on update cascade;

I get:

... "currentsessions_username_fkey" FOREIGN KEY (username)
REFERENCES authorizedusers(username) ON UPDATE CASCADE ON DELETE
CASCADE

Is this a bug or a strange thing I had thought I understood? Or has
it been fixed in 8.2.5 ("Fix incorrect handling of some foreign-key
corner cases (Tom)" -- from the release notes)

If anyone wants more data, just holler.

Thanks
W

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bryan Wilkerson 2007-12-21 20:19:51 self ordering list
Previous Message Marc 2007-12-21 19:10:12 slow query