SQLite级联删除

Question

父表是：

该表如下：

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

子表格如下：

CREATE TABLE BDET (
ID INTEGER primary key asc,
BID INTEGER,
BCODE TEXT,
QTY INTEGER,
FOREIGN KEY (BID) REFERENCES BHEAD(ID) ON DELETE CASCADE
)

我也执行了 SQL 语句 PRAGMA foreign_keys = ON;，但是它没有起作用；当我从 BHEAD 中删除一行时，它在 BDET 中关联的行并没有被删除...

为什么会这样？

- David He

Answer 1

你使用的是哪个版本的SQLite？

请参阅：外键。

为了在SQLite中使用外键约束，必须编译库时未定义 SQLITE_OMIT_FOREIGN_KEY 或 SQLITE_OMIT_TRIGGER。

- jonasbn

---

我无法让它与SQLite3版本3.6.12一起工作，但是在客户端中使用指定的pragma，它可以在SQLite3版本3.7.8中工作。 - jonasbn

Answer 2

然而，您也可以实现on delete cascade以在删除父行时删除所有子行。

-- 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;

- Mohan Reddy