On Delete Cascade in SQLite3

ON DELETE CASCADE in sqlite3

Foreign key support is not enabled in SQLite by default. You need to enable it manually each time you connect to the database using the pragma:

PRAGMA foreign_keys = ON

SQLite: ON DELETE CASCADE is not working right

Foreign key constraints are not active by default. You must enable them with the following command, once per database session :

PRAGMA foreign_keys = ON;

From Official Doc :

2. Enabling Foreign Key Support

Assuming the library is compiled with foreign key constraints enabled,
it must still be enabled by the application at runtime, using the
PRAGMA foreign_keys command. For example:

sqlite> PRAGMA foreign_keys = ON;

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

Delete cascade doesn't work for sqlite

Rows holding foreign keys with delete cascading enabled will delete themselves when the row holding the primary key is deleted. If you delete the row holding the foreign key, the row holding the primary key will not be deleted. This is intentional.

As I understand it, you've a one-to-one or one-to-many relationship where a certain title can can have one or more employees working under it and each employee only has one title.

If it's a one-to-one relationship, you can simply delete the row with the primary key in title_emp. Any employees with foreign keys pointing to that primary key will also be deleted (if you have foreign keys enabled.)

You can enable foreign keys with

PRAGMA foreign_keys = ON;

The same process will work with a one-to-many relationship, if you want to delete a title and all employees working under that title.

If it's a one-to-many relationship and you want the title to be deleted when there are no employees working under that title, cascading delete will be inadequate. you will need to use a trigger instead.

The following trigger will delete the title if the last employee working under that title is deleted.

CREATE TRIGGER delete_unused_title
  AFTER
  DELETE ON
  employees
  BEGIN
    DELETE FROM title_emp WHERE emp_id = OLD.emp_id
    AND (SELECT COUNT(*) FROM employees WHERE emp_id = OLD.emp_id) = 0;
  END;

How to use ON DELETE CASCADE with SQLite and Entity Framework 6

Thank to comments, I found out how to solve this issue.

First I looked at this answer:
Cascade on delete not cascading with EF

The important part is this quote:

The Entity Framework is actually an ADO.NET data provider that is
itself wrapping an ADO.NET data provider (SQLite, to be specific).
Normally, the Entity Framework will open a database connection
whenever it needs one; these automatically-opened connections are
automatically closed when the Entity Framework is finished with it.
This default behavior works well with SQL Server due to its ADO.NET
provider's connection pooling. However, it does not work well with
SQLite, due to various "properties" existing on the SQLite connection
itself. One example is "PRAGMA foreign_keys = ON", which enforces
foreign keys only for that SQLite database connection. If the Entity
Framework opens and closes its connections at will, then SQLite
PRAGMAs such as these are lost.

So, if there is a place to put instructions, it would be only in the connection string.

Consequently I added this:

foreign keys=True

Now it works fine !

python sqlite foreing keys not deleting on cascade

PRAGMA FOREIGN_KEYS = ON operates on a per connection basis and this means that you must set it for every connection object that you obtain (if you want the behavior that it provides, otherwise don't set it because it may decrease performance).

Inside the Database class define:

def set_foreign_keys(self)
    self.conn.execute("PRAGMA FOREIGN_KEYS = ON")

and use it in the client:

from database import Database

db = Database("spotify.db")
db.set_foreign_keys()

db.query("insert into artistas (id_spotify, nome) values (?, ?)", ("1", "The Beatles"))
db.query("insert into musicas (id_spotify, nome, id_artista) values (?, ?, ?)", ("m1", "Hey Jude", 1))

db.query("delete from artistas where id = ?", (1,))

How to make ON DELETE CASCADE work in sqlite 3.7.4?

SQLite foreign keys are disabled for compatibility purposes. You need to enable them manually right after each connection to the database.

con.execute("PRAGMA foreign_keys = ON")

---

Related Topics