| 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 |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| 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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Allan Kamau | 2008-12-11 03:52:20 | Re: Collapsing (select) row values into single text field. |
| Previous Message | Oliveiros Cristina | 2008-12-10 19:21:02 | Re: Best way to "and" from a one-to-many joined table? |