欧洲废物目录代码的母/树参考完整性检查

Question

我正在尝试创建一个数据库，其中一部分涉及存储欧洲废物目录(EWC)代码。

EWC代码是一个分层的2、4或6位代码，其中前2个字符表示章节，后面2个字符表示子章节，最后2个字符表示浪费描述。

例如：

来自农业、园艺、水产养殖、林业、狩猎和渔业、食品准备和加工的02废物

肉的制备和加工过程中产生的0202废物。鱼类及其他动物源性食物

不适合消费或加工的020203材料

需要存储整个树，因此在创建子记录时，我希望确保父树存在。

然而..。我有点困惑该怎么处理这件事。不直接引用列的外键(即:首先调用函数的尝试)触发语法错误：

...
foreign key substring(ewc_code, 1, length(ewc_code) - 2) references cdsw_data.ewc_codes (ewc_code) match simple on update restrict on delete cascade
...

ERROR:  syntax error at or near "substring"
LINE 5:  foreign key substring(ewc_code, 1, length(ewc_code) - 2) re...

有人能给我找个合适的解决办法吗？

表结构：

create table cdsw_data.ewc_codes (
    ewc_code varchar(6) primary key,
    ewc_descrition text,
    constraint valid_ewc check(ewc_code ~ '^(\d\d){1,3}$')
);

Answer 1

我可以想到三种方法：

• 使用3种不同的表，一种是两位数列，一种是两位数列，另一种是两位数列。使用这些作为复合主键。声明对父表列的外键引用。有一个(可能是重载的)函数将完整键计算为一个字符串。
• 使用触发器函数
• 执行所有完整性检查，使用一个可以用作外键的助手列，并断言它具有预期值：

创建表cdsw_data.ewc_codes ( ewc_code varchar(6)主键，ewc_parentcode varchar(4)引用cdsw_data.ewc_codes、ewc_description文本、约束valid_ewc检查(ewc_code~ '^(\d\d){1,3}$')、约束父检查(ewc_parentcode与null_if(左(ewc_code，-2)、‘’)没有区别);