ON DELETE CASCADE Question

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general