Android交叉引用表中的ForeignKey用于什么

ForeignKey添加了强制实施referential integrity的约束(规则).规则是引用父表的子表中的列的值必须是父表的某一行中引用的列中的现有值.简单地说，孤儿是不允许的.

• 查看https://database.guide/what-is-referential-integrity/(或使用类似what is referential integrity的内容进行搜索)

• 应该注意的是，外键冲突将触发异常，而与OR无关...(忽略、替换...).

  • The documentation says
    • The ON CONFLICT clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. The ON CONFLICT algorithm does not apply to FOREIGN KEY constraints. There are five conflict resolution algorithm choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default conflict resolution algorithm is ABORT. This is what they mean:见https://www.sqlite.org/lang_conflict.html

除了规则ACTION之外，还可以在删除或更新父项中的被引用列时发生违反(冲突)时设置帮助维护referential integrity的规则ACTION.最常见/最有用的ACTION是级联，因此有onDelete和onUpdate参数.

你很可能希望提到https://www.sqlite.org/foreignkeys.html

Demo个

主题不是使用Room，而是SQLite是如何工作的.Room是SQLite的包装器，基本上，注释导致生成SQL.

• e.g. look in the generated java for the @Database annotated class suffixed with _Impl and find the createAllTables method (function in Kotlin terms) and you will see the SQL for creating the tables.个

该演示使用SQLite SQL并可放入SQLite工具中，使用的是Navicat for SQLite:-

/* Make sure that the demo environment is tidy*/
DROP TABLE IF EXISTS a_xref_b;
DROP TABLE IF EXISTS a_xref_b_NO_RI;
DROP TABLE IF EXISTS tablea;
DROP TABLE IF EXISTS tableb;

/* Create the core tables a and b */
CREATE TABLE IF NOT EXISTS tablea (id INTEGER PRIMARY KEY /*<<<< PARENT */, a_value TEXT /* etc */); 
CREATE TABLE IF NOT EXISTS tableb (id INTEGER PRIMARY KEY /*<<<< PARENT */, b_value TEXT /* etc */); 

/* Create the cross reference table WITHOUT foreign key constraints */
CREATE TABLE IF NOT EXISTS a_xref_b_NO_RI (a_ref INTEGER, b_ref INTEGER, PRIMARY KEY (a_ref,b_ref));

/* Load some data into the core tables */
INSERT INTO tablea VALUES (1,'Aa'),(2,'Ba'),(3,'Ca'),(4,'Da');
INSERT INTO tableb VALUES (10,'Ab'),(11,'Bb'),(12,'Cb'),(13,'Db');
/* add some cross references including a row where referential integrity does not exist (9999,7777) */
INSERT INTO a_xref_b_NO_RI VALUES (1,11),(1,13),(3,10),(3,12),(9999,7777)/*<<<< CHILDREN THAT DONT EXIST */;

/* RESULT 1 (not a little complicated to show ALL cross references)*/
SELECT ALL 
    *, 
    (SELECT group_concat(a_value)||' : '||group_concat(id) FROM tablea WHERE tablea.id = a_ref) AS from_a,
    (SELECT group_concat(b_value)||' : '||group_concat(id) FROM tableb WHERE tableb.id = b_ref) AS from_b
FROM a_xref_b_NO_RI;
/* DELETE some rows from b*/
DELETE FROM tableb WHERE id > 11 AND id < 1000;
/* RESULT 2 (now what happens) */
SELECT ALL 
    *, 
    (SELECT group_concat(a_value)||' : '||group_concat(id) FROM tablea WHERE tablea.id = a_ref) AS from_a,
    (SELECT group_concat(b_value)||' : '||group_concat(id) FROM tableb WHERE tableb.id = b_ref) AS from_b
FROM a_xref_b_NO_RI;

/*========= FOREIGN KEYS ==========*/
/* Empty table b* */
DELETE FROM tableb;
/* Create the xref table WITH FK constraints and actions */
CREATE TABLE IF NOT EXISTS a_xref_b (
    a_ref INTEGER REFERENCES tablea(id) ON DELETE CASCADE ON UPDATE CASCADE , 
    b_ref INTEGER REFERENCES tableb(id) ON DELETE CASCADE ON UPDATE CASCADE, 
    PRIMARY KEY (a_ref,b_ref)
);
/* Re load table b */
INSERT INTO tableb VALUES (10,'Ab'),(11,'Bb'),(12,'Cb'),(13,'Db');
/* Add the xref rows (NOTE not 9999,7777 as an exception occurr) */
INSERT INTO a_xref_b VALUES (1,11),(1,13),(3,10),(3,12);
/* RESULT 3 (equiv of RESULT 1) */
SELECT ALL 
    *, 
    (SELECT group_concat(a_value)||' : '||group_concat(id) FROM tablea WHERE tablea.id = a_ref) AS from_a,
    (SELECT group_concat(b_value)||' : '||group_concat(id) FROM tableb WHERE tableb.id = b_ref) AS from_b
FROM a_xref_b;
/* do the same delete as above */
DELETE FROM tableb WHERE id > 11 AND id < 1000;
/* update some referenced columns */
UPDATE tablea SET id = id * 10;
/* RESULT 4 (equiv of RESULT 3) */
SELECT ALL 
    *, 
    (SELECT group_concat(a_value)||' : '||group_concat(id) FROM tablea WHERE tablea.id = a_ref) AS from_a,
    (SELECT group_concat(b_value)||' : '||group_concat(id) FROM tableb WHERE tableb.id = b_ref) AS from_b
FROM a_xref_b;
/* TRY TO INSERT orphans */
INSERT OR IGNORE INTO a_xref_b VALUES(9999,7777);
/* CLEAN UP DEMO ENVIRONMENT */
DROP TABLE IF EXISTS a_xref_b;
DROP TABLE IF EXISTS a_xref_b_NO_RI;
DROP TABLE IF EXISTS tablea;
DROP TABLE IF EXISTS tableb;

• 请参阅 comments

RESULTS (output of the 4 SELECTs):-个

• RESULT 1个

• 

  • 高亮显示不存在RI的地方
  • 例如，9999和7777是孤儿(无父母的子元素)

• RESULT 2个

• 

  • 由于从Tableb中删除行而导致的更多孤儿

• RESULT 3 (using FKEYS orphans not allowed)个

• 

  • 没有孤儿(请注意，插入9999,7777将失败(见下文))

• RESULT 4 (after equivalent deletions and update to change referenced parent values for tablea)个

• 

• 可以看到，对表A中的id(乘以10)的更改(更新)已级联到子级，即a_xref_b表中的a_ref列，因此关系保持不变.

• ATTEMPT TO ADD ORPHANS个

• 消息(失败)=

:-

/* TRY TO INSERT orphans */
INSERT OR IGNORE INTO a_xref_b VALUES(9999,7777)
> FOREIGN KEY constraint failed
> Time: 0s

• 请注意，由于故障，清理不会运行