Postgres Pro
Downloads
Home   >   mailing lists

Thread: ON DELETE CASCADE Question

ON DELETE CASCADE Question

From
Jason Long
Date:
I would like for corresponding records in t_a to be deleted when I
delete a record from t_b.  This deletes from t_b when I delete from t_a,
but not the other way around.  I am unable to create a foreign key
constraint on t_a because this table holds records from several other
tables. I added a simple script below that demonstrates my problem.

Any suggestions?

/*******************************************************************/
drop table IF EXISTS t_b;
drop table IF EXISTS t_a;

CREATE TABLE t_a
(
  id bigint NOT NULL,
  CONSTRAINT pk_a PRIMARY KEY (id)
);

CREATE TABLE t_b
(
  id bigint NOT NULL,
  CONSTRAINT pk_b PRIMARY KEY (id),
  CONSTRAINT fk_b_a FOREIGN KEY (id) REFERENCES t_a (id) ON DELETE
CASCADE
);


INSERT INTO t_a VALUES (1),(2),(3);
INSERT INTO t_b VALUES (1),(2),(3);

delete from t_b where id = 2;

select * from t_a;

Re: ON DELETE CASCADE Question

From
Adrian Klaver
Date:
On 11/04/2013 11:44 AM, Jason Long wrote:
> I would like for corresponding records in t_a to be deleted when I
> delete a record from t_b.  This deletes from t_b when I delete from t_a,
> but not the other way around.  I am unable to create a foreign key
> constraint on t_a because this table holds records from several other
> tables.

I am not sure how this is a problem? If you propose to delete a value
from t_a and that value is used by other tables how is the manner of its
deletion relevant?

I added a simple script below that demonstrates my problem.
>
> Any suggestions?
>



--
Adrian Klaver
adrian.klaver@gmail.com

Re: ON DELETE CASCADE Question

From
Adrian Klaver
Date:
On 11/04/2013 11:44 AM, Jason Long wrote:
> I would like for corresponding records in t_a to be deleted when I
> delete a record from t_b.  This deletes from t_b when I delete from t_a,
> but not the other way around.  I am unable to create a foreign key
> constraint on t_a because this table holds records from several other
> tables. I added a simple script below that demonstrates my problem.

As I sent my previous post, it dawned on me what I think you where
trying to say. That the id field has values that have relevance to
tables other than t_b and would not be accepted by a FK to ta_b. In
other words what you are looking for is a conditional FK relationship
between t_a and t_b. AFAIK to get that you will have to roll your own
trigger on t_b.


--
Adrian Klaver
adrian.klaver@gmail.com

Re: ON DELETE CASCADE Question

From
Elliot
Date:
On 2013-11-04 14:44, Jason Long wrote:
> CREATE TABLE t_a
> (
>    id bigint NOT NULL,
>    CONSTRAINT pk_a PRIMARY KEY (id)
> );
>
> CREATE TABLE t_b
> (
>    id bigint NOT NULL,
>    CONSTRAINT pk_b PRIMARY KEY (id),
>    CONSTRAINT fk_b_a FOREIGN KEY (id) REFERENCES t_a (id) ON DELETE
> CASCADE
> );
>
>
> INSERT INTO t_a VALUES (1),(2),(3);
> INSERT INTO t_b VALUES (1),(2),(3);
>
> delete from t_b where id = 2;
>
> select * from t_a;
This depends entirely on your use case and how your data actually relate
to each other, but an alternative to using a trigger to do that delete
you could possibly go with inheritance and avoid the foreign keys
altogether. Presumably the other tables you mention that might have
references to t_a should also be defined as inheriting from A if they
have the same relationship to A that B does.

Example:

CREATE TABLE t_a
(
   id bigint NOT NULL,
   CONSTRAINT pk_a PRIMARY KEY (id)
);

CREATE TABLE t_b
(
   CONSTRAINT pk_b PRIMARY KEY (id)
)
inherits (t_a);

INSERT INTO t_b VALUES (1),(2),(3);

select * from t_a;

delete from t_a where id = 2;

select * from t_a;