Ralf Schneider wrote : >Hi, > >I did not get a real answer to my question two weeks ago, so I >try it again: > >I try to create a table with two foreign key definitions like >the following >statement on a MaxDB 7.5.0.19: > >create table USERS >( > ID integer not null, > USERNAME varchar(100) not null, > CREATED_BY integer, > MODIFIED_BY integer, > primary key (ID), > FOREIGN KEY FK_USER_REFE_USER1 (CREATED_BY) REFERENCES >USERS (ID) ON >DELETE >SET NULL, > FOREIGN KEY FK_USER_REFE_USER2 (MODIFIED_BY) >REFERENCES USERS (ID) ON >DELETE >SET NULL >) > >This statement fails with an error: >SQL error -7040 = Referential constraint not allowed (error >position: 1) > >When I remove one of the foreign key definitions (doesn't >matter which one) >the statment works fine. When I replace the "ON DELETE SET >NULL" by "ON >DELETE CASCADE" also executes without error. > >Why can't I define these two foreign keys? I did not find any >restrictions in >the docs describing this behavior. > >Best regards, >Ralf. > >-- >MaxDB Discussion Mailing List >For list archives: http://lists.mysql.com/maxdb >To unsubscribe: >http://lists.mysql.com/[EMAIL PROTECTED] > > In the documentation (http://dev.mysql.com/doc/maxdb/en/6d/117c5fd14811d2a97400a0c9449261/frameset.htm) the following rule gives the answer :
A reference cycle in which one referential CONSTRAINT definition does not specify CASCADE and all other referential CONSTRAINT definitions specify CASCADE is not allowed. This rule is violated in your example. The rule guarantees unique results independent of the order of execution of the referential constraints. We know that this is too strict in some cases, but there are no plans to change it in the near future. Best Regards, Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
