RE: Query to be clarified!!

[Zabach, Elke]

Harish Kumar G wrote: > Hi, > When I tried to create a table in SAPDB some > ---- Error ------------------------------- > Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed > General error;-7040 POS(1) Referential constraint not allowed. >  > The query which I tried to create is this. If I am trying ON DELETE CASCADE > instead of ON DELETE RESTRICT its working > Please let me know in detail/...... > CREATE TABLE  doc_itm_ref ( >   doc_hdr_id  fixed(19) NOT NULL, >   doc_itm_id smallint NOT NULL, >   ref_doc_id  fixed(19) NOT NULL, >   ref_itm_id smallint NOT NULL, >   PRIMARY KEY (doc_hdr_id,doc_itm_id,ref_doc_id,ref_itm_id), >   FOREIGN KEY (doc_hdr_id) REFERENCES doc_hdr(doc_hdr_id) ON DELETE CASCADE, >   FOREIGN KEY (doc_hdr_id,doc_itm_id) REFERENCES doc_itm(doc_hdr_id,doc_itm_id) >          ON DELETE CASCADE, >   FOREIGN KEY (ref_doc_id) REFERENCES doc_hdr(doc_hdr_id) ON DELETE RESTRICT, >   FOREIGN KEY (ref_doc_id,ref_itm_id) REFERENCES doc_itm(doc_hdr_id,doc_itm_id) >    ON DELETE RESTRICT > )   There is the restriction in SAP DB that no different DELETE-rules are allowed for any sequence starting with table A (perhaps via table B and C) ending at table D. If a referential constraint definition with DELETE CASCADE starting at table doc_hdr is handled first during delete/update, then the result may differ from the result which would occur if the DELETE RESTRICT-constraint was handled first. To avoid such differences caused by the sequence of handled referential constraints, no different DELETE-rules between two tables are allowed.   Sorry, this is no bug, but a feature.   Elke SAP Labs Berlin