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
