Forbidding insertion of integer not in rowid in SQLite (keep directed acyclic graph table consistent)

Question

I'd like to express:

"insertion of record with 'parent' value that is not included in 'rowid' AFTER INSERTION is forbidden."

My intention is to keep the table internally consistent as a directed acyclic graph, with every record being a node referring to its parent (root nodes are their own parent). How can I do that?

Here's what I have (with rowid used as the primary key):

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;

`NEW.rowid` is undefined in a BEFORE INSERT trigger. Why don't you set `parent` to `null` for root nodes? — forpas, Nov 27 '21 at 16:27
@forpas yes, I'm thinking that perhaps the right way to go would be to wrap an `insert` query inside a transaction and rollback if consistency is not maintained? I could use `null` for roots, but does that not add some overhead due to the need to handle `null` separately when traversing the graph? — Raoul, Nov 27 '21 at 16:33
I don't know how you plan to traverse the graph and if null adds any overhead, but if you use null then all you need is referential integrity and no triggers. — forpas, Nov 27 '21 at 16:35

score 1 · Accepted Answer · answered Nov 27 '21 at 16:45

I would suggest to use null values in the column parent for root nodes, because this way all you have to do is add referential integrity to your table.

Add a column id defined as INTEGER PRIMARY KEY, so that it is an alias of the rowid and also make the column parent to reference 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)                                                           
);

Now, turn on foreign key support:

PRAGMA foreign_keys = ON;

and insert rows:

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

This will fail:

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

because there is no row in the table with id = 5.

See the demo.

Forbidding insertion of integer not in rowid in SQLite (keep directed acyclic graph table consistent)

1 Answers1