SQLite Forum

cycle detection in CHECK constraint with recursive CTE
Login
I have a table (schema generated via an ORM) with a hierarchical child-has-parent relationship:

```sql
CREATE TABLE IF NOT EXISTS "tag" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, 
"name" varchar(40) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS "tag_parents" (
"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
"from_tag_id" integer NOT NULL REFERENCES "tag" ("id") DEFERRABLE INITIALLY DEFERRED,
"to_tag_id" integer NOT NULL REFERENCES "tag" ("id") DEFERRABLE INITIALLY DEFERRED
);
```

I wanted to add a CHECK constraint that prevents cycles from being added to the database:

```sql
CONSTRAINT no_cycles CHECK (
   NOT EXISTS (
      WITH RECURSIVE p(root_id, end_id) AS (
          SELECT DISTINCT to_tag_id AS root_id, to_tag_id AS end_id FROM tag_parents WHERE NOT EXISTS (SELECT 1 FROM tag_parents AS s WHERE s.from_tag_id=to_tag_id)
          UNION ALL
          SELECT t.from_tag_id, t.to_tag_id FROM tag_parents AS t
          JOIN p ON t.to_tag_id = p.root_id)
      SELECT 1 FROM p WHERE t=from_tag_id AND f=to_tag_id
    )
)
```

But predictably CREATE with the constraint added fails with `Error: no such table: tag_parents` because of the self reference.

Is detecting a cycle in a constraint possible?