Informix候选键,级联删除,脚本

Seraph115

drop table seraph_a;
drop table seraph_b;

CREATE TABLE seraph_a (
iddecimal(19,0)NOT NULL,
report_idVARCHAR(40)NOT NULL,
bank_idVARCHAR(20)NOT NULL,
report_dateVARCHAR(10)NOT NULL,
grant_user_idVARCHAR(32)NOT NULL,
        PRIMARY KEY (id) CONSTRAINT pk_seraph_a,
        UNIQUE(report_id, bank_id, report_date) CONSTRAINT unique_seraph_a
);

CREATE TABLE seraph_b (
iddecimal(19,0)NOT NULL,
report_idVARCHAR(40)NOT NULL,
bank_idVARCHAR(20)NOT NULL,
report_dateVARCHAR(10)NOT NULL,
grant_user_idVARCHAR(32)NOT NULL,
        PRIMARY KEY (id) CONSTRAINT pk_seraph_b,
        UNIQUE(report_id, bank_id, report_date) CONSTRAINT unique_seraph_b,
        FOREIGN KEY (report_id, bank_id, report_date) REFERENCES seraph_a(report_id, bank_id, report_date)  ON DELETE CASCADE
);

insert into seraph_a  values(1, 'a1', 'a1', 'a1' ,'a1');
insert into seraph_b  values(1, 'a1', 'a1', 'a1' ,'a1');

insert into seraph_a  values(2, 'a2', 'a1', 'a1' ,'a1');
insert into seraph_b  values(2, 'a2', 'a1', 'a1' ,'a1');

delete from seraph_a where report_id='a1' and bank_id='a1' and report_date='a1';