mysql - 关联表中这样的外键约束？

Question

有A、B、C三张表，src列是A表和B表的外键。 Table是A.ID和B.ID的关联表。

mysql 中是否有任何约束以确保插入表 C 的每一行都必须遵循约束:A.src 等于 B.src 的 ID。

例如，在下面的表 C 中，row 1 7 是允许的，因为 A.ID 和 B.ID 的 SRC 都是 35，但是 row 2 7 是非法的，因为 A.ID 的 SRC 是 46，而 B.ID 的 SRC 是 35。

Table A
ID  SRC
1   35
2   46
3   46

Table B
ID  SRC
6   46
7   35
8   46

Table C
A_ID    B_ID
1   7
3   6

是否可以设置任何约束或机制来确保插入表 C 的每一行都遵循常规？

谢谢。

Answer 1

您可以在 Oracle 中使用物化 View 执行此操作:

Oracle 设置:

CREATE TABLE A (
  ID NUMBER PRIMARY KEY,
  SRC NUMBER
);

CREATE TABLE B (
  ID NUMBER PRIMARY KEY,
  SRC NUMBER
);

CREATE MATERIALIZED VIEW LOG ON A
   WITH SEQUENCE, ROWID(id, src)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON B
   WITH SEQUENCE, ROWID(id, src)
   INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW AB_MV
   BUILD IMMEDIATE
   REFRESH FAST ON COMMIT
   AS SELECT a.id, b.id 
      FROM   a, b
      WHERE  a.src = b.src
      GROUP BY A.id, B.id;


ALTER TABLE AB_MV ADD CONSTRAINT AB_MV__A__B__PK PRIMARY KEY (A_ID, B_ID );

CREATE TABLE C (
  A_ID NUMBER REFERENCES A( ID ),
  B_ID NUMBER REFERENCES B( ID ),
  PRIMARY KEY ( A_ID, B_ID ),
  FOREIGN KEY ( A_ID, B_ID ) REFERENCES AB_MV ( A_ID, B_ID )
);

插入数据:

INSERT INTO A
SELECT 1, 35 FROM DUAL UNION ALL
SELECT 2, 46 FROM DUAL UNION ALL
SELECT 3, 46 FROM DUAL;

INSERT INTO B
SELECT 7, 35 FROM DUAL UNION ALL
SELECT 6, 46 FROM DUAL UNION ALL
SELECT 8, 46 FROM DUAL;

SELECT * FROM AB_MV;
-- Will return zero rows as there has not been a commit.

COMMIT;

SELECT * FROM AB_MV;
-- Will return 5 rows.

INSERT INTO C VALUES ( 1, 7 );
-- 1 row inserted

INSERT INTO C VALUES ( 2, 7 );
-- Fails with ORA-02291: Integrity constraint violated - parent key not found.

您可以在 MySQL 中使用触发器来执行类似的操作来填充 AB_MV 表(使用 MERGE INTO 来强制执行唯一性)- 尽管用于从中删除行的触发器A 或 B 可能很有趣，因为 ID 对可以来自多个 SRC 值。