CODEWITHSUNDEEP
sqlitejdbc
SpiderRico
SpiderRico

Reputation: 2026

JDBC ignores SQLite foreign key constraint ON DELETE action

I've a Rate table with the following structre:

CREATE TABLE Rate (
id INTEGER PRIMARY KEY AUTOINCREMENT,
book_id INTEGER, 
value INTEGER,
user_id INTEGER,
FOREIGN KEY(user_id) REFERENCES  User(ID),
FOREIGN KEY(book_id) REFERENCES  book(ID) ON DELETE RESTRICT

)

And a book table with the following structure:

DROP TABLE IF EXISTS Book;
CREATE TABLE Book (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT UNIQUE NOT NULL,
    ISBN VARCHAR,
    author_id INTEGER,
    editor_id INTEGER,
    translator_id INTEGER,
    publisher_id INTEGER,
    "type" VARCHAR,
    language VARCHAR,
    "date" VARCHAR, 
    format VARCHAR, 
    summary TEXT,


    FOREIGN KEY(author_id) REFERENCES Author(ID)    ON DELETE SET NULL ON UPDATE CASCADE, 
    FOREIGN KEY(editor_id) REFERENCES Editor(ID)    ON DELETE SET NULL ON UPDATE CASCADE, 
    FOREIGN KEY(translator_id) REFERENCES Translator(ID)    ON DELETE SET NULL ON UPDATE CASCADE,
    FOREIGN KEY(publisher_id) REFERENCES Publisher(ID)  ON DELETE SET NULL ON UPDATE CASCADE


);

So, if I have a Rate entry like this:

1 1 4 3(ID, BOOK_ID, VALUE, USER_ID)

I shouldn't be able to delete the Book with the ID 1, right? This is exactly what happens when I try to delete the Book with ID 1 on SQLITE Manager. It gives me FOREIGN KEY constraint failed

However, when I call my delete from the code, it totally ignores the restriction and deletes the book and there's no change in the Rate entry it is still:

1 1 4 3

And my delete method is like the following:

public void delete() {
        try {
            String query = "DELETE FROM book WHERE id = ? ";
            PreparedStatement statement = db.prepareStatement(query);
            statement.setInt(1, (Integer) this.id);
            statement.executeUpdate();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

When i run PRAGMA foreign_keys on SQLITE Manager it returns 1. So, I assume that the database is created correctly.

Upvotes: 2

Views: 1598

Answers (1)

CL.
CL.

Reputation: 180020

The documentation says:

... foreign key constraints ... must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:

PRAGMA foreign_keys = ON;

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

Upvotes: 2

Related Questions