在关联表中，像这样的外键约束条件是什么？

Question

有三个表A、B、C，其中表A和B的src列是外键。表C是A.ID和B.ID之间的关联表。

在mysql中是否有约束条件可以确保插入到表C中的每一行都遵循以下约束条件：A.src等于B.src，对于它们的ID。

例如，在下面的表C中，允许第1行7，因为A.ID和B.ID的SRC都是35，但是不允许第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的每一行都遵循规则？

谢谢。

- Junjie

---

Oracle、MySQL和RDB，这是非常不寻常的组合...你真的在使用所有这些产品吗？ - jarlh

我认为这并不罕见，我目前正在使用mysql，并且以前使用过Oracle，我也很想知道其他关系型数据库中是否存在该约束。 - Junjie

不要仅仅标记与产品无关的标签... 标记您正在使用的DBMS产品以面对问题。 您可能已经注意到，MySQL的约束处理方式与Oracle的有些不同... - jarlh

是的，你说得对，MySQL是有重点的，我知道不同的DBMS之间的约束条件是不同的。谢谢你的建议。 - Junjie

我还不是很明白，您说“表C”必须遵循约束条件：“A的SRC等于B的SRC为其ID。”但您还说“第2行7列”是不合法的，因为“A.ID的SRC为46，而B.ID的SRC为35。”如果您看到“第3行6列”都具有相同的“SRC”，那么“表C”中应该有“第3行8列”、“第2行6列”和“第2行8列”，对吗？ - JTR

@JTR 是的，你说得对，“row 3 8”、“row 2 6”和“row 2 8”都是可接受的并且可以插入进去，但这并不意味着它们必须被插入，对吧？ - Junjie

Answer 1

我不确定这个，但是我尝试了一下

INSERT INTO table_c
(SELECT
   a.ID,
   b.ID
 FROM
   table_a AS a INNER JOIN table_b AS b ON a.SRC = b.SRC
);

这个对我来说很有效，可以将table_a和table_b的ID插入到table_c中

- JTR

---

虽然这段代码获取了可接受的值并将它们插入表中，但它完全忽略了问题的关键点，因为它没有强制要求只有在表a和表b中都存在匹配的可接受对且具有相同的src时才允许插入。 - MT0

正如@MT0所说，“它并不强制要求只允许在a和b两个表中都有匹配的可接受对的值，并且这些值具有相同的src”。 - Junjie

@JTR 实际上，如果检查了所有可用的配对，表C就没有太多意义，因为我可以通过A join B轻松地获得它，对吧？ - Junjie

@jjs 是的，我也这么想。我的错，抱歉，请忽略这个回答 XD - JTR

Answer 2

你可以使用 Oracle 中的物化视图来完成此操作： 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值。

- MT0