Currently I'm working on a project (JAVA GUI) where you can manipulate data from a database and all that "basic" stuff. One of the features is the possibility to import data from an Excel file to the DB. I managed to get it working but when it comes to actually execute the batch of inserts I remembered that in order to import I also had to delete former data since the client wants to import it all from scratch on new import.
Problem: Since the major tables needed of importing are the one giving out foreing keys, when it comes to deleting former data and insert new an error occures: ORA-02292 ** which is obvious since there are tables depending on that primary key. Maybe design flaw?
Question: How could I avoid this problem?
I can't provide lines of code since it's confidential information but I can provide a grasp of the DB scheme:
The objective is to be able to delete and insert data to TABLEs 1, 2, 4 and 6.
Using:
- JAVA SWING as GUI;
- APACHE POI as Import;
- ORACLE XE as Database;
CodePudding user response：
Foreign keys or referential integrities need to be organized with cascade operations. Which means if you expect your data from an important table to be deleted then you have to cascade the delete operation to all those table whose data is dependent on that important table.
For eg. if you want to delete an employee 'emp-1' from EMPLOYEE table then you should also delete it's address 'addr-1' from a referenced ADDRESS table. Otherwise an unreferenced address in the ADDRESS table laying there is of no use.
Refer this oracle documentation for more details on cascade delete