Benoit Menendez wrote :
>I get the following error:
>General error;-7040 POS(1) Referential constraint not allowed. with the
>following DML code:
>create table TBL_USERS (
> NAME varchar(64) key
>)
>//
>create table TBL_FOLDERS (
> ID integer key,
> NAME varchar(64) not null,
> USER_NAME varchar(64) references TBL_USERS on delete cascade
>)
>//
>create table TBL_ACCOUNTS (
> ID integer key,
> NAME varchar(64) not null,
> USER_NAME varchar(64) references TBL_USERS on delete cascade,
> FOLDER_ID integer references TBL_FOLDERS on delete set null
>)
>
>If I change the last constraint to:
>
> FOLDER_ID integer references TBL_FOLDERS on delete cascade
>
>It works, but this is NOT what I want...
>
>Am I doing something wrong or did I hit a limitation of SapDB referential
>integrity implementation...
SAPDB has defined some rules for referential constraint structures, which
ensure
that the result of a delete from a referenced table is independent
from the order of execution of the triggered internal statements.
These rules are table based, but should be column based and are therefore
a little bit too strict. That's the case in your example.
We have encountered the problem and will solve it in the future.
Till then maybe you can avoid the problem by use of a trigger :
1. create table TBL_ACCOUNTS without the referential constraint for column
FOLDER_ID.
2. create the following trigger which implements the desired referential
constraint :
create trigger ref_integrity for tbl_folders after delete execute (
LOCK TABLE DBA.TBL_ACCOUNTS IN SHARE MODE;
UPDATE DBA.TBL_ACCOUNTS SET FOLDER_ID = NULL WHERE FOLDER_ID = :OLD.ID;
IF $RC <> 0 AND $RC <> 100
THEN
STOP ($rc, $errmsg);
)
Thomas
---
Thomas Anhaus
SAPDB, SAP Labs Berlin
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
