PostgreSQL引用inscription表字段作外键时报错无匹配唯一约束
问题描述
这是我在Stack Overflow上提出的第一个问题,希望各位能帮忙解答,非常感谢!
我在尝试创建多张数据表时遇到如下报错:ERROR: there is no unique constraint matching given keys for referenced table "inscription"
我的建表SQL语句如下:
CREATE TABLE ue ( id_ue varchar(30) primary key, designation varchar(50) NOT NULL, ects int NOT NULL ); CREATE TABLE auditeur ( id_auditeur int primary key, nom varchar(30) NOT NULL, prenom varchar(30) NOT NULL, date_nais varchar(30) NOT NULL ); CREATE TABLE tarif ( id_tarif int primary key, tarif varchar(30) NOT NULL ); CREATE TABLE inscription ( annee int, id_auditeur int, id_tarif int, PRIMARY KEY (annee, id_auditeur), FOREIGN KEY (id_auditeur) REFERENCES auditeur (id_auditeur), FOREIGN KEY (id_tarif) REFERENCES tarif (id_tarif) ); CREATE TABLE reglement ( id_auditeur int, annee int, num_ordre int, montant int NOT NULL, primary key (id_auditeur, annee, num_ordre), FOREIGN KEY (id_auditeur) REFERENCES auditeur (id_auditeur), FOREIGN KEY (annee) REFERENCES inscription (annee) ); CREATE TABLE inscrire ( id_auditeur int, annee int, id_ue varchar(30), note1 int, note2 int, primary key (id_auditeur, annee, id_ue), FOREIGN KEY (id_auditeur) REFERENCES auditeur (id_auditeur), FOREIGN KEY (annee) REFERENCES inscription (annee), FOREIGN KEY (id_ue) REFERENCES ue (id_ue) );
我的建表逻辑需遵循如下逻辑模型:
逻辑模型示意图
对应的数据库关系模式如下:
关系模式示意图
我已测试验证:仅执行到inscription表创建完成时可正常运行,但创建最后两张reglement、inscrire表时会触发报错,因为这两张表的外键都引用了annee字段。
我无法为inscription表的annee字段添加唯一约束:这是课程作业,后续需插入包含不同年份(annee)的测试数据,若添加唯一约束PostgreSQL会抛出约束冲突错误。我原本认为只要annee属于主键组成部分就无需额外添加唯一约束,目前完全不清楚问题所在,感谢各位的帮助!
问题解答
核心规则:外键引用的列集合,必须与被引用表上的某一个*完整的唯一约束(主键本身就是非空的唯一约束)的列集合一一对应,不允许仅引用复合唯一约束的部分列。
你的inscription表使用的是(annee, id_auditeur)组成的复合主键,单独的annee列没有独立的唯一约束(同一年份可以存在多个不同auditeur的注册记录),因此直接用FOREIGN KEY (annee) REFERENCES inscription (annee)会触发约束匹配错误。
解决方案
不需要给annee单独添加唯一约束,只需要修改reglement和inscrire表的外键定义,同时引用inscription复合主键的全部两列即可:
- 修改
reglement表的外键:
将原有的FOREIGN KEY (annee) REFERENCES inscription (annee)替换为FOREIGN KEY (annee, id_auditeur) REFERENCES inscription (annee, id_auditeur) - 修改
inscrire表的外键:
将原有的FOREIGN KEY (annee) REFERENCES inscription (annee)替换为FOREIGN KEY (annee, id_auditeur) REFERENCES inscription (annee, id_auditeur)
修改后的两张表完整定义如下:
CREATE TABLE reglement ( id_auditeur int, annee int, num_ordre int, montant int NOT NULL, primary key (id_auditeur, annee, num_ordre), FOREIGN KEY (id_auditeur) REFERENCES auditeur (id_auditeur), FOREIGN KEY (annee, id_auditeur) REFERENCES inscription (annee, id_auditeur) ); CREATE TABLE inscrire ( id_auditeur int, annee int, id_ue varchar(30), note1 int, note2 int, primary key (id_auditeur, annee, id_ue), FOREIGN KEY (id_auditeur) REFERENCES auditeur (id_auditeur), FOREIGN KEY (annee, id_auditeur) REFERENCES inscription (annee, id_auditeur), FOREIGN KEY (id_ue) REFERENCES ue (id_ue) );
该改法完全匹配你的业务逻辑:缴费记录、选课记录都是绑定“某auditeur在某一年的注册记录”,同时引用两个主键列既保证了外键的合法性,也不会产生额外的唯一约束,后续插入多年份测试数据不会触发冲突。
内容的提问来源于stack exchange,提问作者Jil0

