禁止在SQLite中插入不在rowid中的整数(保持有向无环图表的一致性)

Question

我想表达：

"禁止在插入后插入"rowid"中不包含的具有"parent"值的记录">

我的意图是将表作为一个有向无环图保持内部一致，每个记录都是一个引用其父节点的节点(根节点是它们自己的父节点(。我该怎么做？

以下是我所拥有的(以rowid作为主键(：

CREATE TABLE Heap (                                                                                                       
name   TEXT     CHECK(typeof(name) = 'text')                                                           
NOT NULL                                                                               
UNIQUE                      ,                                       
parent INTEGER  DEFAULT rowid               ,                                       
color  INTEGER  CHECK(color BETWEEN 0 AND 2)                                                           
);                                                                                                    
                                                
CREATE TRIGGER parent_not_in_rowid                                                                                        
BEFORE INSERT ON Heap                                                                                                     
BEGIN                                                                                                                     
SELECT RAISE(FAIL, 'parent id inconsistent') FROM Heap                                                                   
WHERE NOT EXISTS(SELECT 1 FROM Heap WHERE NEW.rowid = NEW.parent);                                                       
END;

Answer 1

我建议在根节点的列parent中使用null值，因为这样您所要做的就是为表添加引用完整性

添加定义为INTEGER PRIMARY KEY的列id，使其成为rowid的别名，并使列parent引用id:

CREATE TABLE Heap ( 
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE CHECK(typeof(name) = 'text'),
parent INTEGER REFERENCES Heap(id),                                       
color INTEGER CHECK(color BETWEEN 0 AND 2)                                                           
); 

现在，打开外键支持：

PRAGMA foreign_keys = ON;

和插入行：

INSERT INTO Heap (name, parent, color) VALUES ('name1', null, 1);
INSERT INTO Heap (name, parent, color) VALUES ('name2', 1, 1);

这将失败：

INSERT INTO Heap (name, parent, color) VALUES ('name3', 5, 2);

因为在具有CCD_ 8的表中没有行

请参阅演示