Re: deleting a foreign key that has no references

On 3/19/07, Glen W. Mabey <Glen(dot)Mabey(at)swri(dot)org> wrote:
> > write a triggers which do that.
> I understand that a trigger should be written, and I have already
> implemented two such triggers, as described above.

no, i think i didn't make myself clear.
let's use this situation:
we have tables:
create table x (id serial primary key, some_text text);
create table y (id serial primary key, x_id int4 not null references x
(id), some_field text);
where table x is your table in which you want to make some deletes,
and table y is some table that has foreign key to it.
now, you add to table x a field:
alter table x add column refcount int4 not null default 0;

and then we add a trigger:
CREATE OR REPLACE FUNCTION some_trg() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE x SET refcount = refcount + 1 WHERE id = NEW.x_id;
ELSIF TG_OP = 'UPDATE' THEN
IF NEW.x_id <> OLD.x_id THEN
UPDATE x SET refcount = refcount + 1 WHERE id = NEW.x_id;
UPDATE x SET refcount = refcount - 1 WHERE id = OLD.x_id;
END IF;
ELSIF TG_OP = 'DELETE' THEN
UPDATE x SET refcount = refcount - 1 WHERE id = OLD.x_id;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER some_trg AFTER INSERT OR UPDATE OR DELETE ON y FOR EACH
ROW EXECUTE PROCEDURE some_trg();

then - you have to populate the refcount field with current value, but
this is easily doable, and as far as i know you already are doing it
in your code.

so - the trigger keeps the refcount up to date. it is quite
lightweight, so shouldn't be a problem. and what's more important -
size of the table trigger is on doesn't matter.

simple, and working.

depesz

--
http://www.depesz.com/ - nowy, lepszy depesz