I understand parent table to child table modifications with ON UPDATE/ON DELETE CASCADS. It's very good. Now, I stuck with child to parent tables query optimization after normalization.
Scenario: sqlite> pragma foreign_keys = on; sqlite> CREATE TABLE artist(artistid INTEGER PRIMARY KEY AUTOINCREMENT, artistname TEXT); sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY AUTOINCREMENT, trackname TEXT, trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE); for ex: Five tracks are pointing to one artist. it needs to delete track by track. For first track to four tracks, it should not delete artist information from artist table. For fifth track, it should delete track information along with artist information. Is it possible to do with single query? On Mon, Nov 11, 2013 at 3:08 PM, dd <durga.d...@gmail.com> wrote: > Hi, > > Thanks Simon. > > sqlite> pragma foreign_keys = on; > sqlite> CREATE TABLE artist(artistid INTEGER PRIMARY KEY > AUTOINCREMENT, artistname TEXT); > sqlite> CREATE TABLE track(trackid INTEGER PRIMARY KEY > AUTOINCREMENT, trackname TEXT, trackartist INTEGER REFERENCES > artist(artistid) ON UPDATE CASCADE ON DELETE CASCADE); > > How do I insert trackname as "That's Amore" and artistname as "Dean > Martin" with single query in artist and track tables? > > dd > > > > > On Sun, Nov 10, 2013 at 6:45 PM, Simon Slavin <slav...@bigfraud.org> wrote: >> >> On 10 Nov 2013, at 10:54am, dd <durga.d...@gmail.com> wrote: >> >>> I have two tables in my database. >>> >>> After applying normalization, there are twelve tables with foreign >>> key support. >>> >>> For insert/delete operations, it has to execute twelve queries >>> instead of two. Is it recommended way? >> >> You should not have to write twelve queries in your own programming. If you >> have set up your FOREIGN KEYs correctly, SQLite should be doing that for >> you. The foreign keys facility should have allowed you to move some of your >> business model from your own programming into SQLite, which should be >> dealing with it in one place in a consistent way. >> >>> In delete case, do always need to check in parent table whether >>> child key refers to any other rows in parent table? >> >> Again, if you have set up your foreign keys correctly, SQLite should be >> doing the checking or deleting for you. Whether SQLite will prevent you >> from deleting, or delete rows from other tables, depends which of these >> actions you have set up: >> >> <http://www.sqlite.org/foreignkeys.html#fk_actions> >> >> Simon. >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
