Re: ON DELETE CASCADE Question

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;