SQLite - cross reference search
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 746
- Joined: Sun Feb 04, 2007 11:01 pm
- Location: McKenna, WA
Re: SQLite - cross reference search
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.
Tom
MacBook Pro OS Mojave 10.14
MacBook Pro OS Mojave 10.14
Re: SQLite - cross reference search
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,
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,
-
- Posts: 746
- Joined: Sun Feb 04, 2007 11:01 pm
- Location: McKenna, WA
Re: SQLite - cross reference search
Thanks a lot, Adrien. That's much simpler than I thought it would be.
Tom
MacBook Pro OS Mojave 10.14
MacBook Pro OS Mojave 10.14
-
- Posts: 746
- Joined: Sun Feb 04, 2007 11:01 pm
- Location: McKenna, WA
Re: SQLite - cross reference search
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
Tom
MacBook Pro OS Mojave 10.14
MacBook Pro OS Mojave 10.14