Hi,
Thanks for your suggestion Dennis. Now I normalized the database as follows: * CREATE TABLE ALBUM (AlbumId INTEGER NOT NULL PRIMARY KEY,Album Text);" * CREATE TABLE ARTIST (ArtistId INTEGER NOT NULL PRIMARY KEY,Artist Text);" * CREATE TABLE BGM (BgmId INTEGER NOT NULL PRIMARY KEY,Bgm Text);" * CREATE TABLE MUSIC (Id INTEGER NOT NULL PRIMARY KEY, Album_Id INTEGER CONSTRAINT fk_Album_id REFERENCES ALBUM(AlbumId)ON DELETE CASCADE, Artist_Id INTEGER NOT NULL CONSTRAINT fk_Artist_id REFERENCES ARTIST(ArtistId) ON DELETE CASCADE, Bgm_Id INTEGER NOT NULL CONSTRAINT fk_Bgm_id REFERENCES BGM(BgmId) ON DELETE CASCADE );" The following are the triggers: // -- Foreign Key Preventing insert "CREATE TRIGGER fki_MUSIC_Album_Id_ALBUM_AlbumId BEFORE INSERT ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table MUSIC violates foreign key constraint fki_MUSIC_Album_Id_ALBUM_AlbumId') WHERE NEW.Album_Id IS NOT NULL AND (SELECT AlbumId FROM ALBUM WHERE AlbumId = NEW.Album_Id) IS NULL;END;" //-- Foreign key preventing update "CREATE TRIGGER fku_MUSIC_Album_Id_ALBUM_AlbumId BEFORE UPDATE ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table MUSIC violates foreign key constraint fku_MUSIC_Album_Id_ALBUM_AlbumId') WHERE NEW.Album_Id IS NOT NULL AND (SELECT AlbumId FROM ALBUM WHERE AlbumId = NEW.Album_Id) IS NULL;END;" //-- Cascading Delete "CREATE TRIGGER fkdc_MUSIC_Album_Id_ALBUM_AlbumId BEFORE DELETE ON ALBUM FOR EACH ROW BEGIN DELETE FROM MUSIC WHERE MUSIC.Album_Id = OLD.AlbumId; END;" //-- Foreign Key Preventing insert "CREATE TRIGGER fki_MUSIC_Artist_Id_ARTIST_ArtistId BEFORE INSERT ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table MUSIC violates foreign key constraint fki_MUSIC_Artist_Id_ARTIST_ArtistId') WHERE (SELECT ArtistId FROM ARTIST WHERE ArtistId = NEW.Artist_Id) IS NULL; END;" //-- Foreign key preventing update "CREATE TRIGGER fku_MUSIC_Artist_Id_ARTIST_ArtistId BEFORE UPDATE ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table MUSIC violates foreign key constraint fku_MUSIC_Artist_Id_ARTIST_ArtistId') WHERE (SELECT ArtistId FROM ARTIST WHERE ArtistId = NEW.Artist_Id) IS NULL; END;" //-- Cascading Delete "CREATE TRIGGER fkdc_MUSIC_Artist_Id_ARTIST_ArtistId BEFORE DELETE ON ARTIST FOR EACH ROW BEGIN DELETE FROM MUSIC WHERE MUSIC.Artist_Id = OLD.ArtistId; END;" //-- Foreign Key Preventing insert "CREATE TRIGGER fki_MUSIC_Bgm_Id_BGM_BgmId BEFORE INSERT ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'insert on table MUSIC violates foreign key constraint fki_MUSIC_Bgm_Id_BGM_BgmId') WHERE (SELECT BgmId FROM BGM WHERE BgmId = NEW.Bgm_Id) IS NULL; END;" //-- Foreign key preventing update "CREATE TRIGGER fku_MUSIC_Bgm_Id_BGM_BgmId BEFORE UPDATE ON [MUSIC] FOR EACH ROW BEGIN SELECT RAISE(ROLLBACK, 'update on table MUSIC violates foreign key constraint fku_MUSIC_Bgm_Id_BGM_BgmId') WHERE (SELECT BgmId FROM BGM WHERE BgmId = NEW.Bgm_Id) IS NULL; END;" //-- Cascading Delete "CREATE TRIGGER fkdc_MUSIC_Bgm_Id_BGM_BgmId BEFORE DELETE ON BGM FOR EACH ROW BEGIN DELETE FROM MUSIC WHERE MUSIC.Bgm_Id = OLD.BgmId; END;" My Database table looks similar like this: There is only one record in the database with the following values, ALBUM Table ARTIST Table BGM Table AlbumId : 1 ArtistId : 2 BgmId : 5 Album :Confession Artist : Madonna Bgm : rock MUSIC Table Id : 7 Album_Id : 1 Artist_Id : 2 Bgm_Id : 5 I have only one ALBUM named 'Confession' with id 1 and only one ARTIST named 'Madonna' with id 2 and one BGM named 'rock' with id 5. MUSIC table will have all these details. Now If I delete the Album 'Confession' From table ALBUM. Then it is deleting that record with that id i.e,1 for 'confession' in MUSIC table.So now there is no records in the MUSIC table. But it is not deleting in the ARTIST and the BGM table since that Artist ' Madonna ' and BGM 'rock' are present in one record and that record too deleted in Music table. For me the complete record details to be deleted in all the tables. I think I have to add some more constraints in triggers/sql statemets Can anyone please help me to solve this . Do i need to normalize anything in the create statements? Thanks & Regards, Sreedhar.A
