Got it. Can I conclude this way: Foreign keys works pretty well when application deals with parent keys only. But, application may need to execute more queries when dealing with child key/tables.
Is it? Thanks, dd On Mon, Nov 11, 2013 at 5:35 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 11 Nov 2013, at 12:38pm, dd <durga.d...@gmail.com> wrote: > >> 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? > > You can't do this with foreign keys. You could do it with triggers: when you > delete a track, check to see whether that artist still has any tracks, and if > not, delete the artist. > > But you probably shouldn't. For example, in my own database an artist has > both tracks and albums. Even if I delete the last track by an artist, they > may still have albums under their name. Think of, for example, the Jools > Holland album where every track is a collaboration between him and someone > else. And also, even if I don't have any music by a particular artist, the > artists still exists. > > So I would do clearing up of artists in a separate part of the system, > perhaps a monthly maintenance task, rather than having it happen > automatically. > > 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
