execution error in query

[p***@yahoo.com]

i need the record to be deleted from XORDERS table when an ORDER_ID gets deleted from ORDERS table.
But when I execute the query,i face an exception.
create table XORDERS (
ORDERS_ID BIGINT NOT NULL ,
ACCOUNT_ID BIGINT NOT NULL references ACCOUNT(ACCOUNT_ID),
constraint XORDERS_F1 foreign key (ORDERS_ID) references ORDERS (ORDERS_ID) on delete cascade);

SQL Exception: Foreign Key 'XORDERS_F1' is invalid because 'the delete rule of foreign key must be NO ACTION. (The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).) '.

I tried with NO ACTION.It works.But that doesnt satisfy my requirement.

Plz help me out.

[d***@blueyonder.co.uk]

i'd try making orders_id the primary key of your xorders table ie

create table XORDERS (
ORDERS_ID BIGINT NOT NULL ,
ACCOUNT_ID BIGINT NOT NULL references ACCOUNT(ACCOUNT_ID),
constraint XORDERS_P primary key(ORDERS_ID),
constraint XORDERS_F1 foreign key (ORDERS_ID) references ORDERS (ORDERS_ID) on delete cascade);

[p***@yahoo.com]

I tried the same.

SQL Exception: Foreign Key 'XORDERS_F1' is invalid because 'the delete rule of foreign key must be NO ACTION. (The relationship would cause the table to be delete-connected to the same table through multiple relationships and such relationships must have the same delete rule (NO ACTION, RESTRICT or CASCADE).) '.


The query i tried is..

create table XORDERS (
ORDERS_ID BIGINT NOT NULL ,
ACCOUNT_ID BIGINT NOT NULL references ACCOUNT(ACCOUNT_ID),
USERS_ID BIGINT NOT NULL references USERS(USERS_ID),
constraint XORDERS_P primary key(ORDERS_ID),
constraint XORDERS_F1 foreign key (ORDERS_ID) references ORDERS (ORDERS_ID) on delete cascade);

Paul.

[p***@yahoo.com]

When i remove the foriegn keys n try.it works with cascade.

i saw the below delete rule in in net.
A cycle involving two or more tables must not cause a table to be delete-connected to itself. Thus, if the relationship would form a cycle:

"The referential constraint cannot be defined if each of the existing relationships that would be part of the cycle have a delete rule of CASCADE.

CASCADE must not be specified if T2 is delete-connected to T1."

If this the case ,how can it be resolved.

[geo]

These are no longer deleted via cascade within the dbclean, but deleted
bottom up, so your foreign keyed EJB needs to be No Action as that as you
noted is the updated rule on ORDERS

Post by p***@yahoo.com
When i remove the foriegn keys n try.it works with cascade.
i saw the below delete rule in in net.
A cycle involving two or more tables must not cause a table to be
"The referential constraint cannot be defined if each of the existing
relationships that would be part of the cycle have a delete rule of
CASCADE.
CASCADE must not be specified if T2 is delete-connected to T1."
If this the case ,how can it be resolved.