通常,当您拆分一个键时,会遇到这种问题。修复该拆分键,然后使用重叠的外键约束通常是您要寻找的解决方案。
create table cmember (
cmemberid integer primary key,
username varchar(15) not null,
emailaddress varchar(64) not null
);
create table application (
applicationid integer primary key,
description varchar(50) not null
);
create table member_application (
cmemberid integer not null references cmember (cmemberid),
applicationid integer not null references application (applicationid),
primary key (cmemberid, applicationid)
);
create table role (
roleid integer primary key,
rolename varchar(25) not null
);
create table crole (
croleid integer not null references role (roleid),
applicationid integer not null references application (applicationid),
primary key (croleid, applicationid)
);
create table member_role (
cmemberid integer not null references cmember (cmemberid),
croleid integer not null,
applicationid integer not null,
primary key (cmemberid, croleid, applicationid),
foreign key (croleid, applicationid) references crole (croleid, applicationid),
foreign key (cmemberid, applicationid) references member_application (cmemberid, applicationid)
);
insert into cmember values (1, 'A', 'A@b.com');
insert into cmember values (2, 'B', 'B@b.com');
insert into application values (1, 'App 1');
insert into application values (2, 'App 2');
insert into member_application values (1, 1);
insert into member_application values (2, 2);
insert into role values (1, 'Admin');
insert into crole values (1, 1);
insert into crole values (1, 2);
insert into member_role values (1, 1, 1);
insert into member_role values (2, 1, 2);
成员1只被分配给应用程序1。因此,尝试插入引用应用程序2的行应该会失败。
insert into member_role values (1,1,2);
ERROR: insert or update on table "member_role" violates foreign key constraint "member_role_cmemberid_fkey1"
DETAIL: Key (cmemberid, applicationid)=(1, 2) is not present in table "member_application".