0

I try to delete all the subcategories and entries within this subentries, when i delete the main categorie from within one table. If i had multiple tables, i would use a foreign key with ON DELETE CASCADE. But this seems not to work, when i use only a single table. I have the following example table:

CREATE TABLE "data" ( `ID` TEXT NOT NULL, `ParentID` TEXT NOT NULL, `Title` TEXT NOT NULL )

ID is not an INT, because its generated as random UUID. If in ParentID the same value is stored, as in ID, this entry is a subentry to the corresponding one with the same ID. This creates a hierarchy like structure. If i delete now one entry, it should delete also all subentries to the deleted ID (and its sub-sub-sub entries, if there are some).

That's why i tried to use a foreign Key:

CREATE TABLE "data" ( `ID` TEXT, `ParentID` TEXT, `Title` TEXT, FOREIGN KEY (`ParentID`) REFERENCES "data"(`ID`) ON DELETE CASCADE )

But i get an error: foreign key mismatch - "data" referencing "data" (DELETE FROM data WHERE ID = 8;)

BTW: Is there a difference in using the above foreign key definition vs:

CREATE TABLE "data" ( `ID` TEXT, `ParentID` TEXT, `Title` TEXT, CONSTRAINT data_fk1 FOREIGN KEY (`ParentID`) REFERENCES "data"(`ID`) ON DELETE CASCADE )
Improve this question
1
  • Sorry, forget that to mention: SQLite and MySQL Commented Oct 28, 2022 at 7:37

2 Answers 2

Reset to default
1

For sqllite you need to enable foreign keys as:

PRAGMA foreign_keys = ON;

In general you need to reference something that is unique in the parent table, i.e.:

CREATE TABLE data ( ID TEXT NOT NULL UNIQUE -- Primary key is the normal way , ParentID TEXT , Title TEXT , FOREIGN KEY (ParentID) REFERENCES data(ID) ON DELETE CASCADE );

If you instead use

CREATE TABLE data ( ID TEXT NOT NULL UNIQUE -- Primary key is the normal way , ParentID TEXT , Title TEXT , CONSTRAINT whatever FOREIGN KEY (ParentID) REFERENCES data(ID) ON DELETE CASCADE );

The only difference is that you get control of the name of the constraint. If you need to alter the constraint in any way, you know how to reference it. I prefer it over getting a random name. In fact I prefer to add my constraints separate from the table definition, but for reasons unknown to me I could not get that to work for sqllite. See SQLlite Fiddle and MySQL Fiddle. MySQL complained about the text type in keys, so I switched it to INT.

Other things to consider, choose identifiers that does not have to be quoted.

Your data table does in some sense contain two different kinds of fact: (id, name) and (id, parentid) which is why you have to treat the root node(s) in a special way. Either allow null for parentid or have this node reference it self. You may consider splitting your table in two:

CREATE TABLE nodes ( node_id ... NOT NULL PRIMARY KEY , node_name ... NOT NULL ); CREATE TABLE tree ( node_id ... NOT NULL PRIMARY KEY , parent_id ... NOT NULL FOREIGN KEY REFERENCES tree(node_id) ... ); INSERT INTO nodes (node_id, node_name) VALUES (1,'One'), (2, 'Two'); INSERT INTO tree (node_id, parent_id) VALUES (2, 1);

Since node 'One' does not have any parent, we don't need a row for that. It is in a sense a more clean model with separated facts or duties for the two tables. In practice they are often merged into one (like in your example) for performance reasons, but one should beware of the logical quirk.

Improve this answer
1
  • 1
    You cannot add a FOREIGN KEY constraint with ALTER TABLE in SQLite. See sqlite.org/lang_altertable.html (where there is a convoluted procedure to do it through other commands). Commented Oct 28, 2022 at 10:22
1

@Lennart: Many thx for the explanation. I got it to work with:

CREATE TABLE data ( `ID` TEXT NOT NULL, `ParentID` TEXT, `Title` TEXT, `userid` INTEGER NOT NULL, PRIMARY KEY(`ID`,`userid`), FOREIGN KEY(`userid`) REFERENCES `users`(`userid`) ON DELETE CASCADE, FOREIGN KEY(`ParentID`,`userid`) REFERENCES `data`(`ID`,`userid`) ON DELETE CASCADE );

Because ID is sometimes not unique, but in combination with the userid, it is unique. Additionally the hint the first entry set parentid to null, was a key to success.

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.