我有两个主表,一个保存products
,另一个保存与产品相关的costs
。问题是,我需要一个链接表来合并结合两者(lookup
表),因为成本只知道代码,而产品只知道类。(这个结构是出于历史数据的原因……)
我需要构造一个外键,以确保每个产品都有价格。
一个简单的MWE将是(目前没有FK)
-- only has class; no code...
CREATE TABLE products (
class TEXT NOT NULL,
color TEXT NOT NULL,
price REAL,
UNIQUE(class)
-- THIS is what I want; but b.c. COMBINED is a view and not a table, I get
-- the error later
-- FOREIGN KEY (class, color) REFERENCES COMBINED (class, color)
);
-- only code no class...
CREATE TABLE costs (
code TEXT NOT NULL,
color TEXT NOT NULL,
cost REAL,
UNIQUE(code, color)
);
-- link table for products and costs
CREATE TABLE lookup (
class TEXT NOT NULL,
code TEXT NOT NULL,
UNIQUE(class)
);
CREATE VIEW COMBINED AS
SELECT costs.code, lookup.class, costs.color
FROM costs LEFT JOIN lookup ON costs.class = lookup.class;
然后,测试数据:
INSERT INTO lookup (class, code) VALUES ('AAA', 'aaa'), ('BBB', 'bbb');
INSERT INTO costs (code, color, cost) VALUES ('aaa', 'red', 123), ('bbb', 'blue', 456);
INSERT INTO products (class, color, price) VALUES ('AAA', 'red', 0.123); -- should work
INSERT INTO products (class, color, price) VALUES ('CCC', 'red', 0.456); -- expect FK error on CCC
当我使用外键作为删除时,我得到错误foreign key mismatch - "products" referencing "COMBINED"
,因为COMBINED是一个视图而不是一个表。
有没有什么方法可以做到这一点,或者我需要玩弄触发器,以确保成本总是有一个匹配的列?
1条答案
按热度按时间mfuanj7w1#
我还是觉得你把事情弄得太复杂了
锁定表是桥接表
✓
✓
✓
✓
✓
✓
✓
✓
✓
✓
fiddle