Re: Foreign Key 'walker'?
От | Erwin Moller |
---|---|
Тема | Re: Foreign Key 'walker'? |
Дата | |
Msg-id | 49242C71.2060004@darwine.nl обсуждение исходный текст |
Ответ на | Re: Foreign Key 'walker'? (Erwin Moller <erwin@darwine.nl>) |
Список | pgsql-general |
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 >> >> >> > >
В списке pgsql-general по дате отправления: