Erwin Moller schreef:
Craig Ringer schreef:
Erwin Moller wrote:
No, that is not the kind of chicken I was talking about. ;-)
My chicken is more along these lines:
I often have some tables to which everything is related (eg
tblcourse that contains everything belonging to a certain course).
I don't want to make a single simple mistake that if I accidentally
delete an entry there, I lose all underlying data via the CASCADE.
OK, so the idea is to prevent DELETEs of records with existing
relationships, except when invoked via some sort of script or wrapper
that says, essentially, "Yes, I really do mean delete this record and
all its related records, I'm not just accidentally issuing a DELETE".
Personally, if I had to do this I'd do this with a PL/PgSQL function
that dug through pg_catalog or INFORMATION_SCHEMA to do a depth-first
search and delete of the related records. Frankly, though, it sounds
horrible, and if you run into a relationship loop you're in a real
mess. At least the latter problem can't bite you unless you use
DEFERRED constraints.
[Pfft my english sucks, I added a few clarifications]
Hi Craig,
Thanks for your reply.
For clearity's sake: If I want to delete a record that is refered to,
I always simply first delete the 'lower' records that fit my criteria,
then the 'higher'.
So I often end up with a series of deletes, which when executed in
that right order, do the same as a CASCADE would do on the 'higher'
record.
So that is the way I prefer to do the deletions.
Reason is simply I rather hit a FK constraint than a cascading delete
on mistake.
(I am programming against postgresql in almost all my projects, thus
this mistake just happens from time to time.)
So, that is the way I prefer doing it: It keeps me sharp because I
force myself to always understand each relation in every table that in
in 'the chain'.
So I am NOT looking for help on writing such a wrapper/script/function
to do this, because I prefer doing it myself.
I do not mind making a few deletes (allthough they get more and more
complex if you have more levels).
I asked this weird question because it would come in handy if I could
get the list of tables that are connected via FK to my table in question.
Is this clear? Maybe I have a weird way of programming. ;-)
No problem at all.
I totally agree with you.
I only have this fear I screw up (not Postgresql) if I use CASCADE
and accidentally delete a 'high' record in the chain.
To me, that sounds like you might have some of your relationships
backwards. Generally I wouldn't want to set an ON DELETE CASCADE
relationship on a parent record (not does it usually make any sense)
; rather, the relationship on the child record will have ON DELETE
CASCADE set so the child will be deleted if the parent is. Deleting a
child record should only delete the child record, never cascade up to
a parent.
I must have written very poorly, since that is NOT what I mean.
(I am not a native english speaker, so indulge me please).
I totally agree with the statement that a DELETE should NEVER cascade
up to the parent record, and I'll never design a database like that.
Possibly my poor understanding of CASCADE is the root of this
confusion. (I wrote already I never use it).
I thought that is I define a field in a table with 'ON DELETE CASCADE'
that means that ANY record in other tables that have a FK constraint
on this parenttable are also deleted.
And the same for child-child-tables, etc.
Typo again: The 'ON DELETE CASCADE' is not for a column (field) but for
the table.
Sorry to be so sloppy. ;-)
Regards,
Erwin Moller
Do I have that right?
The child record is useless and meaningless without the parent, so
this is appropriate.
For a practical example in a course/student management tool: If you
delete a `student', and the student has `student_course' (an m:n
mapping table) entries referencing `course', you would not expect the
course to be deleted, only the student<->course relationship and the
student. If the course was deleted explicitly by the user, you'd
expect the student_course relationship to restrict the deletion if
students were still listed as taking the course. So, the natural
definition would be:
CREATE TABLE student_course (
student_id INTEGER REFERENCES student(student_id) ON DELETE CASCADE,
course_id INTEGER REFERENCES course(course_id) ON DELETE NO ACTION,
PRIMARY KEY(student_id, course_id)
);
... which is pretty close to what you end up with if you just bang
out the obvious structure for the relationship.
Well, that is excactly the way I work too. So we agree here.
To stick to this example: I was describing the situation I accidently
DELETED course_id in table course.
I don't want that that deletion cascades though the whole database and
deletes all related rows.
But maybe I misinterpret the way CASCADE works (see my explanation
above).
Regards,
Erwin Moller
There are odd cases where those relationships end up being reversed
(or at least bidirectional), and in those cases I do tend to avoid ON
DELETE CASCADE, instead providing functions, triggers or rules to
clean up appropriately.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
