create table orderCombine ( Id VARCHAR2(16) not null, OrderId VARCHAR2(15) not null, FormerId VARCHAR2(16) not null, constraint PK_ORDERCOMBINE primary key ( FormerId) ) /必须同时把ORDERID和FORMERID作为主键。这一点是不能改动的。 不然整个系统的逻辑就不通了。 如果是这样的话,这两个表之间实现级联删除是否就是不可能的了。
oracle要求外键关联的必须是唯一索引或主键,因此还是用触发器吧 CREATE OR REPLACE TRIGGER TRG_DELINFO BEFORE DELETE ON OrderCombine FOR EACH ROW BEGIN DELETE FROM VIPForm WHERE VIPForm.Id=:OLD.FormerId; END TRG_DELINFO;
"Id" VARCHAR2(16) not null,
"OrderId" VARCHAR2(15) not null,
"FormerId" VARCHAR2(16) not null,
constraint PK_ORDERCOMBINE primary key ("OrderId", "FormerId")
)
/
create table "VIPForm" (
"Id" VARCHAR2(16) not null,
"IsValid" CHAR(1) default '0' not null
constraint PK_VIPFORM primary key ("Id")
)
/要求当表OrderCombine删除一条数据时,根据字段FormerId删除表VIPFORM(对应字段为ID)中的数据,
Id VARCHAR2(16) not null,
OrderId VARCHAR2(15) not null,
FormerId VARCHAR2(16) not null,
constraint PK_ORDERCOMBINE primary key ( FormerId)
)
/create table VIPForm (
Id VARCHAR2(16) not null,
IsValid CHAR(1) default '0' not null ,
constraint fk_vipform foreign key(id) references ordercombine(formerid) on delete cascade
);insert into orderCombine values('1','1','1');
insert into orderCombine values('2','2','2');insert into vipform values('1','5');
insert into vipform values('2','4');
insert into vipform values('1','2');SQL> select * from orderCombineID ORDERID FORMERID
---------------- --------------- -------------
1 1 1
2 2 2 SQL> select * from VIPForm;ID I
---------------- -
1 5
2 4
1 2 SQL> delete orderCombine where formerid='1'
2 /已删除 1 行。SQL> commit;提交完成。SQL> select * from orderCombine;ID ORDERID FORMERID
---------------- --------------- --------------
2 2 2 SQL> select * from VIPForm;ID I
---------------- -
2 4 做了适当修改,看看是不是合求求
然后再定义级联删除!!!
Id VARCHAR2(16) not null,
OrderId VARCHAR2(15) not null,
FormerId VARCHAR2(16) not null,
constraint PK_ORDERCOMBINE primary key ( FormerId)
)
/必须同时把ORDERID和FORMERID作为主键。这一点是不能改动的。
不然整个系统的逻辑就不通了。
如果是这样的话,这两个表之间实现级联删除是否就是不可能的了。
CREATE OR REPLACE TRIGGER TRG_DELINFO
BEFORE DELETE ON OrderCombine
FOR EACH ROW
BEGIN
DELETE FROM VIPForm WHERE VIPForm.Id=:OLD.FormerId;
END TRG_DELINFO;