SQLite - cross reference search

[quailcreek]

Thanks, Adrien. I agree. Trial and error is a good way to learn... frustrating but good. I've tried to make this work without the CrossRef table but no-joy. All I need to work out now is deleting a basket from the CrossRef table, deleting a fruit from a basket and deleting a fruit from the CrossRef table. If you have any suggestions I really like to hear them. Thanks again for all the help.

[Adrien]

Have a look there:
https://sqlite.org/lang_createtable.html

in the column def -> column contraint -> foreign key clause
you will see a "on delete cascade" possibility.

So here you have your MyFruit table and you CrossRef table. The CrossRef has a reference to the MyFruit table, and you chose to add the "on delete cascade" statement on that reference when you create the table.

This means, if you delete the source of the reference (in the MyFruit table), the referring row (in the CrossRef table) is also deleted (cascade)! Otherwise, you will have integrity errors, with references on no longer existing fruits in your CrossRef table. Or you will need two DELETE statements, one for the CrossRef and only then, for the MyFruit.

With the "on delete cascade", you just need to write the delete statement: DELETE FROM MyFruit WHERE MyFruit.FruitID = .......
But beware, such statements need to be handled with caution, there are no warnings on delete.

This was for completely deleting a fruit. You can do the same to completely delete a basket.

If you want to remove a fruit from a basket, you just need to DELETE FROM CrossRef WHERE CrossRef.FruitID = ....... AND CrossRef.BasketID = ........
If you need to empty a basket, just DELETE FROM CrossRef WHERE CrossRef.BasketID = ........
If you need to remove a fruit from every baskets, just DELETE FROM CrossRef WHERE CrossRef.FruitID = ........

You can work your way through with that

Cheers,

[quailcreek]

Thanks a lot, Adrien. That's much simpler than I thought it would be.

[quailcreek]

So, Adrien. Is the CREATE TABLE statement really as simple as this?

Code: Select all

 put "CREATE TABLE CrossRef (FruitID INTEGER REFERENCES MyFruit ON DELETE CASCADE, BaksetID INTEGER REFERENCES MyBaskets ON DELETE CASCAD, PRIMARY KEY (FruitID, BacketID))" into tSQLStatement
revExecuteSQL sDatabaseID, tSQLStatement