SQLite cascading delete

The parent table is:

CREATE TABLE BHEAD (
ID INTEGER primary key asc,
DESCR TEXT,
LINECTR INT,
UNITCTR INT)

The child table is:

CREATE TABLE BDET (
ID INTEGER primary key asc,
BID开发者_如何学Python INTEGER,
BCODE TEXT,
QTY INTEGER,
FOREIGN KEY (BID) REFERENCES BHEAD(ID) ON DELETE CASCADE
)

I also execute the SQL PRAGMA foreign_keys = ON;. However, it does not work; when I delete one row from BHEAD, its associated rows in BDET are not gone...

Why was that?

---

What version of SQLite are you using?

Please see: Foreign Keys.

In order to use foreign key constraints in SQLite, the library must be compiled with neither SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined.

---

However, you can also implement on delete cascade to delete all child rows when a parent row is deleted.

-- Create the test tables using ON DELETE CASCADE
DROP TABLE t3 PURGE;
--DROP TABLE t2 PURGE;
--DROP TABLE t1 PURGE;

CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT t1_pk PRIMARY KEY (id)
);

CREATE TABLE t2 (
  id             NUMBER,
  t1_id          NUMBER,
  description    VARCHAR2(50),
  CONSTRAINT t2_pk PRIMARY KEY (id),
  CONSTRAINT t2_t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE CASCADE
);

CREATE TABLE t3 (
  id             NUMBER,
  t2_id          NUMBER,
  description    VARCHAR2(50),
  CONSTRAINT t3_pk PRIMARY KEY (id),
  CONSTRAINT t3_t2_fk FOREIGN KEY (t2_id) REFERENCES t2 (id)
);


INSERT INTO t1 VALUES (1, 't1 ONE');

INSERT INTO t2 VALUES (1, 1, 't2 ONE');
INSERT INTO t2 VALUES (2, NULL, 't2 TWO');

INSERT INTO t3 VALUES (1, 1, 't3 ONE');
INSERT INTO t3 VALUES (2, NULL, 't3 TWO');
COMMIT;


SELECT (SELECT COUNT(*) FROM t1) AS t1_count,
       (SELECT COUNT(*) FROM t2) AS t2_count,
       (SELECT COUNT(*) FROM t3) AS t3_count
FROM   dual;

DELETE FROM t3;

rollback;

truncate table t1 ;

rollback;

truncate table t1 CASCADE;