层叠/删除
层叠/删除
提问于 2016-06-09 22:04:52
我有一个表，其中包含来自多个表的“附件列表”。当我删除主表中的主键时，它应该删除注册i。如果我有一个主表和一个从表，这是正确的。但是，如果我有一个以上的主表，并且想要实现级联删除，它会抛出违反主键和外键的错误消息。因为附件表中的注册in不会同时出现在表1和表2中。
在删除表1和表2中的注册is时，有没有办法实现级联，或者应该在附件中使用常规的delete方法。
表:附件注册is -这是外键附件名称varchar2(50)
主表1: department1注册is -这是主键部门名称varchar2(25)
主表2: department2注册is -这是主键departmentadd varchar2(25)
提前感谢您的帮助。
包含一些要插入的数据的CREATE语句。
CREATE TABLE DEPARTMENT
(
ENROLLID NUMBER,
DEPARTNAME VARCHAR2(100),
CONSTRAINT DEPARTMENT_PK PRIMARY KEY (ENROLLID)
) ;
INSERT INTO DEPARTMENT (ENROLLID, DEPARTNAME) VALUES (1, 'DEPART1');
INSERT INTO DEPARTMENT (ENROLLID, DEPARTNAME) VALUES (2, 'DEPART2');
CREATE TABLE DIVISION
(
ENROLLID NUMBER,
DIVISIONNAME VARCHAR2(100),
CONSTRAINT DIVISION_PK PRIMARY KEY (ENROLLID)
);
INSERT INTO DIVISION (ENROLLID, DIVISIONNAME) VALUES (3, 'DIV1');
INSERT INTO DIVISION (ENROLLID, DIVISIONNAME) VALUES (4, 'DIV2');
CREATE TABLE ATTACHMENTS
(
ENROLLID NUMBER,
FILENAME VARCHAR2(500),
CONSTRAINT ATTACHMENTS_FK FOREIGN KEY (ENROLLID)
REFERENCES Department(ENROLLID) ON DELETE CASCADE
--REFERENCES DIVISION(ENROLLID) ON DELETE CASCADE
)
the above sql creates the table with foreign key constraint. but, it throws error message if include the second CASCADE statement. So, i commented the second reference. Then, i ran the below INSERT statements. The first two were inserted fine. third line threw an error message (ORA-02291 Integrity constraint violated - parent key not found).
INSERT INTO ATTACHMENTS (ENROLLID,FILENAME) VALUES (1, 'attachment1');
INSERT INTO ATTACHMENTS (ENROLLID,FILENAME) VALUES (2, 'attachment1');
INSERT INTO ATTACHMENTS (ENROLLID,FILENAME) VALUES (3, 'attachment1');
INSERT INTO ATTACHMENTS (ENROLLID,FILENAME) VALUES (4, 'attachment1')；
回答 1
Stack Overflow用户
发布于 2016-06-10 19:00:34
为了实现您的目标，我认为您需要一个额外的表，位于附件表与部门表和分部表之间。
可能是这样的：
create table department
(dept_id number,
departname varchar2(100),
constraint department_pk primary key (dept_id)
) ;
insert into department (dept_id, departname) values (1, 'DEPART1');
insert into department (dept_id, departname) values (2, 'DEPART2');
create table division
(div_id number,
divisionname varchar2(100),
constraint division_pk primary key (div_id)
);
insert into division (div_id, divisionname) values (1, 'DIV1');
insert into division (div_id, divisionname) values (2, 'DIV2');
create table enrollment
(enrollid number,
dept_id number,
div_id number,
constraint enrollment_pk primary key (enrollid),
constraint enrollment_dept_fk foreign key (dept_id) references department (dept_id),
constraint enrollment_div_fk foreign key (div_id) references division (div_id),
constraint enrollment_chk check (div_id is not null or dept_id is not null));
--- here, I have assumed that at least one of div_id and dept_id is populated.
--- If it should be that only one of them can be populated, not both, then you'll need to change the check constraint accordingly.
insert into enrollment (enrollid, dept_id, div_id) values (1, 1, null);
insert into enrollment (enrollid, dept_id, div_id) values (2, 2, null);
insert into enrollment (enrollid, dept_id, div_id) values (3, null, 1);
insert into enrollment (enrollid, dept_id, div_id) values (4, null, 2);
create table attachments
(
enrollid number,
filename varchar2(500),
constraint attachments_fk foreign key (enrollid)
references enrollment (enrollid) on delete cascade
);
insert into attachments (enrollid,filename) values (1, 'attachment1');
insert into attachments (enrollid,filename) values (2, 'attachment2');
insert into attachments (enrollid,filename) values (3, 'attachment3');
insert into attachments (enrollid,filename) values (4, 'attachment4');
commit;然后，当您从注册表中删除时，您可以看到该行将自动从attachments表中删除。
delete from enrollment where enrollid = 4;
commit;
select * from enrollment order by enrollid;
ENROLLID DEPT_ID DIV_ID
---------- ---------- ----------
1 1
2 2
3 1
select * from attachments order by enrollid;
ENROLLID FILENAME
---------- -----------
1 attachment1
2 attachment2
3 attachment3页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/37728237
复制相关文章
相似问题
腾讯云开发者
