![]() ![]() In practice they are often merged into one (like in your example) for performance reasons, but one should beware of the logical quirk. The essential syntax for a defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement includes the following: CONSTRAINT symbol FOREIGN KEY indexname (colname. It is in a sense a more clean model with separated facts or duties for the two tables. Since node 'One' does not have any parent, we don't need a row for that. NOT NULL FOREIGN KEY REFERENCES tree(node_id). You may consider splitting your table in two: CREATE TABLE nodes Either allow null for parentid or have this node reference it self. 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. Other things to consider, choose identifiers that does not have to be quoted. MySQL complained about the text type in keys, so I switched it to INT. 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. If you need to alter the constraint in any way, you know how to reference it. The only difference is that you get control of the name of the constraint. , CONSTRAINT whatever FOREIGN KEY (ParentID) REFERENCES data(ID) ON DELETE CASCADE Also, verify that your tables are actually using the InnoDB engine, and that the foreign keys are defined. I have two foreign keys in transactions table and thats way its not. SET foreignkeychecks 1 NOTE: this affects only the current session. However I had tried to delete payment record in phpMyAdmin and cascading delete. , FOREIGN KEY (ParentID) REFERENCES data(ID) ON DELETE CASCADE To enable foeign key constraints, set to the variable to 1. ( ID TEXT NOT NULL UNIQUE - Primary key is the normal way In general you need to reference something that is unique in the parent table, i.e.: CREATE TABLE data ![]() That's why i tried to use a foreign Key: CREATE TABLE "data"įOREIGN KEY (`ParentID`) REFERENCES "data"(`ID`) ON DELETE CASCADEīut i get an error: foreign key mismatch - "data" referencing "data" (DELETE FROM data WHERE ID = 8 )īTW: Is there a difference in using the above foreign key definition vs: CREATE TABLE "data"ĬONSTRAINT data_fk1 FOREIGN KEY (`ParentID`) REFERENCES "data"(`ID`) ON DELETE CASCADEįor sqllite you need to enable foreign keys as: PRAGMA foreign_keys = ON 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). If in ParentID the same value is stored, as in ID, this entry is a subentry to the corresponding one with the same ID. ID is not an INT, because its generated as random UUID. I have the following example table: CREATE TABLE "data" But this seems not to work, when i use only a single table. If i had multiple tables, i would use a foreign key with ON DELETE CASCADE. I try to delete all the subcategories and entries within this subentries, when i delete the main categorie from within one table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |