Skip site navigation (1) Skip section navigation (2)

Is it possible to only allow deletes from a table via referential integrity cascades?

From: "Christopher Maier" <maier(at)med(dot)unc(dot)edu>
To: pgsql-sql(at)postgresql(dot)org
Subject: Is it possible to only allow deletes from a table via referential integrity cascades?
Date: 2008-12-10 19:55:51
Message-ID: 8F55A6E9-8A56-4261-B35A-5A7012D2C8CA@med.unc.edu (view raw or flat)
Thread:
Lists: pgsql-sql
I have two tables joined by a foreign key constraint:

> CREATE TABLE test_master(
>     id SERIAL PRIMARY KEY,
>     foo TEXT
> );
>
> CREATE TABLE test_detail(
>     id SERIAL PRIMARY KEY,
>     master BIGINT NOT NULL REFERENCES test_master(id) ON DELETE  
> CASCADE ON UPDATE CASCADE,
>     bar TEXT
> );

Is there a way to block deletes on the "test_detail" table that will  
only allow rows to be deleted if it is the result of deleting the  
corresponding "test_master" record?  In other words, I'd like to  
disallow direct DELETE commands like this:

> DELETE FROM test_detail WHERE id = 1;

while allowing a command like

> DELETE FROM test_master WHERE id = 1;

to subsequently delete via CASCADE all "test_detail" rows that  
reference test_master # 1.

I've tried using rules and revoking privileges, but both of these  
approaches fail when trying to delete from "test_master".

Thanks in advance,

Chris


pgsql-sql by date

Next:From: Allan KamauDate: 2008-12-11 03:52:20
Subject: Re: Collapsing (select) row values into single text field.
Previous:From: Oliveiros CristinaDate: 2008-12-10 19:21:02
Subject: Re: Best way to "and" from a one-to-many joined table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group