Self referencing foreign keys unsupported
benkilimnik opened this issue · 1 comments
benkilimnik commented
Encountered in the sample application schema for commento located in experiments/schema-annot/annotated/commento-annotated.sql
In the table comments
we would like to have a self referencing foreign key on the column parentHex
with ACCESSED_BY
to support deletion of the row when the parent comment given by parentHex
is deleted.
CREATE TABLE comments ( \
commentHex TEXT NOT NULL UNIQUE PRIMARY KEY, \
domain TEXT NOT NULL, \
path TEXT NOT NULL, \
commenterHex TEXT NOT NULL, \
parentHex TEXT NOT NULL, \
score INTEGER NOT NULL, \
state TEXT NOT NULL, \
FOREIGN KEY (commenterHex) OWNED_BY commenters(commenterHex), \
FOREIGN KEY (domain) ACCESSED_BY domains(domain), \
ON DEL parentHex DELETE_ROW, \
FOREIGN KEY (parentHex) ACCESSED_BY comments(parentHex), \ -- > throws error
ON GET parentHex ANON (commenterHex, score, state), \
ON GET domain ANON (commenterHex, score, state) \
);
K9db throws this error:
INVALID_ARGUMENT: FK points to nonexisting table
KinanBab commented
This is now supported for ACCESSED_BY
ACCESSES
, OWNED_BY
, OWNS
are not support yet.