Hi, I have a problem with an ON DELETE SET NULL-Constraint, maybe connected to the case I found in the mail-groups (I attached the mail at the end) I say "maybe", because the weird behavior I am trying to trace is following:
I have set up a referential constraint with an ON DELETE SET NULL directive. I then delete the related row (which should bring up a null in the FK-column). What happens now is that the column is not set to "null" but to 0 (zero). But the 0 (zero) is actually violating the referential constraint that is set, because there is no key with the value of 0 (zero) in the referenced table. I was lucky to work with JDBC and DBVisualizer (which is a great tool, by the way). It shows me a 0, whereas SQL-Studio shows an error: // Native error -811 [SAP AG][SQLOD32.DLL][SAP DB]Numeric value out of range;-811 Numeric output parameter overflow. // and the row in which the error appears is not shown at all! So without JDBC I would not have been able to see where the problem is. I tried to build a simpler sample that brings up the problem but I couldn't. Maybe the description of the case gives you an Idea what could be wrong. I just wanted to let you know. IMHO this shouldn't happen, since it is an inconsistent state of the database. Regards and thanks for the great database, Manfred "Zabach, Elke" schrieb: (8 Nov 2001) > Robert Kr�ger wrote: > > >>>>> > we are getting the error message in the subject for 'on delete set null' > referential integrity rules and don't understand why. in all the cases there > > is no way that anything cyclic could be happening. could anyone from sap > elaborate on what rules are enforced that trigger this (IMHO incorrect) > error > msg and thus rejection of the constraint? the schema is rather large so I'm > not sending the sql. any other way I can provide you with more info (like a > log entry or something). > <<<<< > > in June we had another guy with the same (?) problem. The answer to him > is appended, too. > If you are sure to have another problem, we need some more info about > your schema. > > Elke > SAP Labs Berlin > > Here is the old mail with the answer : > > Flemming Frandsen wrote: > > >I've been using on delete with my foreign keys but doing so I have seen > >some strange constraints. > > >It seems that if one fk is ON DELETE CASCADE then all of the others must > >be as well, is that true? > > Let fk1 be another foreign key of the table T. This one must be ON DELETE > CASCADE, > if there is a table P and two different ways to T following the foreign keys > REFERENcing P : > > P > / \ > T1 T2 > fk \ / fk1 (must be ON DELETE CASCADE) > T > > This rule has been introduced, to be sure that the result of a delete > statement > of P is independent from the order in which the triggered actions are > executed. > However, we have already noticed, that the rules may be too strict. > If you define foreign keys that don't have a common REFERENced table (P), > there > are no restriction for the delete rules. > > Thomas _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
