Problem with foreign key when deleting artist in java

Question

Problem with foreign key when deleting artist in java

Navigation

#1 by (3 votes)
#2 by (0 votes)
#3 by (0 votes)
#4 by (-1 votes)

1

Good, I'm doing a program to add / remove / modify artists from a database and when I try to delete the artist I skip this:

com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Can not delete or update a parent row: a foreign key constraint fails ( chinook . album , CONSTRAINT FK_AlbumArtistId FOREIGN KEY ( ArtistId ) REFERENCES artist ( ArtistId ) ON DELETE NO ACTION ON UPDATE NO ACTION) at sun.reflect.NativeConstructorAccessorImpl.newInstance0 (Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance (Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance (Unknown Source) at java.lang.reflect.Constructor.newInstance (Unknown Source) at com.mysql.jdbc.Util.handleNewInstance (Util.java:404) at com.mysql.jdbc.Util.getInstance (Util.java:387) at com.mysql.jdbc.SQLError.createSQLException (SQLError.java:932) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3878) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3814) at com.mysql.jdbc.MysqlIO.sendCommand (MysqlIO.java:2478) at com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:2625) at com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2551) at com.mysql.jdbc.PreparedStatement.executeInternal (PreparedStatement.java:1861) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal (PreparedStatement.java:2073) at com.mysql.jdbc.PreparedStatement.executeUpdateInternal (PreparedStatement.java:2009) at com.mysql.jdbc.PreparedStatement.executeLargeUpdate (PreparedStatement.java:5094) at com.mysql.jdbc.PreparedStatement.executeUpdate (PreparedStatement.java:1994) at modelo.dao.ArtistaDAO.eliminarArtista (ArtistDAO.java:103) at vista.VistaArtistasSearch.actionPerformed (VistaArtistasSearch.java:164) at javax.swing.AbstractButton.fireActionPerformed (Unknown Source) at javax.swing.AbstractButton $ Handler.actionPerformed (Unknown Source) at javax.swing.DefaultButtonModel.fireActionPerformed (Unknown Source) at javax.swing.DefaultButtonModel.setPressed (Unknown Source) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased (Unknown Source) at java.awt.Component.processMouseEvent (Unknown Source) at javax.swing.JComponent.processMouseEvent (Unknown Source) at java.awt.Component.processEvent (Unknown Source) at java.awt.Container.processEvent (Unknown Source) at java.awt.Component.dispatchEventImpl (Unknown Source) at java.awt.Container.dispatchEventImpl (Unknown Source) at java.awt.Component.dispatchEvent (Unknown Source) at java.awt.LightweightDispatcher.retargetMouseEvent (Unknown Source) at java.awt.LightweightDispatcher.processMouseEvent (Unknown Source) at java.awt.LightweightDispatcher.dispatchEvent (Unknown Source) at java.awt.Container.dispatchEventImpl (Unknown Source) at java.awt.Window.dispatchEventImpl (Unknown Source) at java.awt.Component.dispatchEvent (Unknown Source) at java.awt.EventQueue.dispatchEventImpl (Unknown Source) at java.awt.EventQueue.access $ 500 (Unknown Source) at java.awt.EventQueue $ 3.run (Unknown Source) at java.awt.EventQueue $ 3.run (Unknown Source) at java.security.AccessController.doPrivileged (Native Method) at java.security.ProtectionDomain $ JavaSecurityAccessImpl.doIntersectionPrivilege (Unknown Source) at java.security.ProtectionDomain $ JavaSecurityAccessImpl.doIntersectionPrivilege (Unknown Source) at java.awt.EventQueue $ 4.run (Unknown Source) at java.awt.EventQueue $ 4.run (Unknown Source) at java.security.AccessController.doPrivileged (Native Method) at java.security.ProtectionDomain $ JavaSecurityAccessImpl.doIntersectionPrivilege (Unknown Source) at java.awt.EventQueue.dispatchEvent (Unknown Source) at java.awt.EventDispatchThread.pumpOneEventForFilters (Unknown Source) at java.awt.EventDispatchThread.pumpEventsForFilter (Unknown Source) at java.awt.EventDispatchThread.pumpEventsForHierarchy (Unknown Source) at java.awt.EventDispatchThread.pumpEvents (Unknown Source) at java.awt.EventDispatchThread.pumpEvents (Unknown Source) at java.awt.EventDispatchThread.run (Unknown Source)

I think I know what the problem is, and that I should do on delete cascade , but I do not know how to do it so that the program executes it only once, since I can not make changes in the database itself, everything must be through the program.

sql java

asked by NeoChiri 07.05.2016 в 18:51

source

4 answers

Problem when forcing ssl in my domain with htaccess Is it possible to include a dll in a jar with eclipse?

score 3 · Answer 1

It is not necessary to activate the on delete cascade , the exception MySQLIntegrityConstraintViolationException indicates that a violation of the integrity of the data is being committed, before eliminating a Artist you have to eliminate all the tuples of the table AlbumArtist that have a reference to that tuple of Artist.

You can do it with independent sentences within the transaction such as:

for(AlbumArtist someAlbumArtist :someArtist.getAlbums()){
   AlbumDAO.delete(someAlbumArtist);
}

ArtistDAO.delete(someArtist);

Where someAlbum and someArtist are related (all albums have to be deleted)

Or alternatively, you can activate the on delete cascade as you intuited, because there will never be an album without an artist and it is logical to eliminate them together with it.

Do not forget to do commit(); at the end of everything.

score 0 · Answer 2

This is because there are existing records related to the frk that has been deleted then it generates a conflict since it is looking for a data that no longer exists in the database, before deleting a frk it is recommended to erase all the tuples corresponding to it and then eliminate the frk

score 0 · Answer 3

You can not delete the records of the Artist table since the foreign key depends on other records of another table, first delete the records of other tables that contain the foreign key of Artist, first your program have it eliminate the table album_artist

 delete from album_artista ab inner join artista ar on ab.artista_id = ar.artista_id  where ab.artista_id = ?

score -1 · Answer 4

on delete Cascade is something specific to your structure and is defined there. It is not possible to apply it for a particular case, it is a characteristic of your structure and your tables that will be invariable with time. What I recommend is to emulate this on delete cascade in java or where it suits you best, creating functions that allow you to erase all the records associated with that artist in the other tables. Because the tables are handled by indexes it will not cause any performance problems, if you have no idea how to create these functions, I can provide them.