How can I delete row from parent table has a referenced column to child table?

Question

I'm using JDBC in javaFx project with SQlite database. I have two tables "PROJECT" and "SMAllPROJECT" such as PROJECT has this structure PROJECT(name,date,state) and SMAllPROJECT has this structure SMAllPROJECT(name,date,state,*project_name*). Project_name is referenced from name column of PROJECT table.

I'm trying to delete row from PROJECT and the rows referenced in SMAllPoject should be deleted. In sqlite studio I configure onCascade method when I apply delete a row from parent table and every thing is well when I test it in SQlite studio, but in my code it deleted rows just from parent table.

This is my code :

Controller method

public void ExecuteDeleteProject() {

        if (!SearchIdProjectSupp.getText().isEmpty()) {

            Project project = new ProjectDao().FindString(SearchIdProjectSupp.getText());
            new ProjectDao().Delete(project);
            String title = "Suppression";
            String message = "Vous avez supprimé le projet " + SearchIdProjectSupp.getText() + ".";
            NotificationType notification = NotificationType.SUCCESS;
            TrayNotification tray = new TrayNotification();
            tray.setTitle(title);
            tray.setMessage(message);
            tray.setNotificationType(notification);
            tray.setAnimationType(AnimationType.SLIDE);
            tray.setImage(new Image("Images/check.png"));
            tray.setRectangleFill(Paint.valueOf("#a8a9fe"));
            tray.showAndDismiss(Duration.seconds(4));
            SearchIdProjectSupp.setText("");
            SuppPaneProject.setVisible(false);
            DeleteProjetButton.setDisable(true);
            CountP.setVisible(true);
            CountP();
            CountR();
            CountPP();

        }

    }

DAO pattern method

public boolean Delete(Project Object) {

        try {

            String queryDeletePerson = "DElETE FROM PROJECT WHERE name=" + "'" + Object.getName() + "'";//Query Insertion in Person_Table

            PreparedStatement preparedStatementPerson = Dbaconnection.getConnection().prepareStatement(queryDeletePerson);//Prepared statement i use this for high performance 

            preparedStatementPerson.execute();

        } catch (SQLException ex) {

            Logger.getLogger(EMPDao.class.getName()).log(Level.SEVERE, null, ex);
        }

        return true;
    }

And this is my DDL

CREATE TABLE PROJECT (
    NAME VARCHAR2 (100) PRIMARY KEY,
    STATE  VARCHAR2 (100),
    DATEDBT DATE,
 );

CREATE TABLE SMALLPROJECT (
    NAMEPROJECT       VARCHAR2 (100) REFERENCES PROJECT (NAME) ON DELETE CASCADE
                                                          ON UPDATE CASCADE,
    NAME VARCHAR2 (20)  PRIMARY KEY,
    DATEDBT     DATE,
    STATE VARCHAR2 (20)  PRIMARY KEY

    );

Can you share the tables' definitions please (not just the column list, but the actual DDLs used to create them)? — Mureinik, Sep 23 '17 at 14:00
When you establish your JDBC connection to the SQLite database do you explicitly enable foreign keys, either by executing `PRAGMA foreign_keys = ON` or by using an `SQLiteConfig` object? — Gord Thompson, Sep 23 '17 at 14:15
@MenaiAlaEddine these DDLs produce multiple errors. Are you sure these are your DDLs? — Mureinik, Sep 23 '17 at 14:22
@GordThompson i'm using SQLite JDBC driver and `stmnt.execute("PRAGMA foreign_keys = ON")` is not implemented by SQLite JDBC driver — Menai Ala Eddine - Aladdin, Sep 23 '17 at 14:39
I just tested with "sqlite-jdbc-3.18.0.jar" from the [Maven Central repository](https://search.maven.org/#search%7Cgav%7C1%7Cg%3A%22org.xerial%22%20AND%20a%3A%22sqlite-jdbc%22) and it works fine. — Gord Thompson, Sep 23 '17 at 15:02
@GordThompson I used SQLiteConfig object and it works perfect,thanks. — Menai Ala Eddine - Aladdin, Sep 23 '17 at 15:29

score 2 · Accepted Answer · answered Sep 23 '17 at 15:17

According to SQLite Foreign Key Support and the answer of How do you enforce foreign key constraints in SQLite through Java? ,it should configure database connection before execute any query.I had this problem because i did not enforce foreign key constraints.

public static final String DB_URL = "jdbc:sqlite:database.db";  
public static final String DRIVER = "org.sqlite.JDBC";  

public static Connection getConnection() throws ClassNotFoundException {  
    Class.forName(DRIVER);  
    Connection connection = null;  
    try {  
        SQLiteConfig config = new SQLiteConfig(); //I add this configuration 
        config.enforceForeignKeys(true);  
        connection = DriverManager.getConnection(DB_URL,config.toProperties());  
    } catch (SQLException ex) {}  
    return connection;  
}

How can I delete row from parent table has a referenced column to child table?

1 Answers1