I’m pretty new to databases and SQLite. I have a database that was provided to me, for an ASP.NET MVC web application to consume. I have to implement CRUD operations with Albums, Tracks, Artists and Media Types. Most of the functionality has been implemented and working as should, however, when implementing the delete operation for the tracks table I noticed an error, ‘[19:46:57] Error while deleting row from table tracks: FOREIGN KEY constraint failed’.
Could someone help me understand why I can not delete a row from the ‘tracks’ table without it violating a foreign key constraint, and which constraint it is violating.
Any assistance would be very much appreciated!
Here is a screenshot of the ERD Diagram, Tracks Table Structure, Tracks Table Constraints and Sample Data.
Advertisement
Answer
I believe that your issue is that the track is referenced by an invoice_item That is you likely have a Foreign Key definition for the TrackID column of the invoice_items table referencing the TrackId column of the Tracks table.
As highlighted :-
Regarding the comment
all the foreign keys have ‘ON UPDATE NO ACTION ON DELETE NO ACTION’
NO ACTION is the default i.e. you take NO ACTION when the conflict occurs so the conflict remains as it is and hence the error. Perhaps consider other actions as per https://sqlite.org/foreignkeys.html#fk_actions