SQLite - 如何删除父行同时保持子/行的？ [英] SQLite - How to delete parent row while keeping the child/children rows?

问题描述

我理解 PRAGMA foreign_key 和 ON DELETE RESTRICT / NO ACTION 的概念，但是我面对不同种类的情况。

我需要删除一个父行，但保持子行与它关联。例如：

  CREATE TABLE A（id，name）; 
 INSERT INTO A（id，name）VALUES（1，Loreum）; 
 
 CREATE TABLE B（id，id_A，name）FOREIGN KEY（id_A）REFERENCES A（id）; 
 INSERT INTO B（id，id_A，name）VALUES（1,1，Opium）; 
从某个地方删除id = 1; 
  

我想在保持子行保持不变的情况下实现此 。这是可能的吗？

编辑

从这个问题。例子可能会帮助一些只有在有代码的情况下才能理解的人。

http://www.sqlite.org/foreignkeys.html#fk_deferredrel =nofollow>延期外键约束：

  PRAGMA foreign_keys = on; 
 CREATE TABLE A（id PRIMARY KEY，name）; 
 INSERT INTO A（id，name）VALUES（1，Loreum）; 
 CREATE TABLE B（id，id_A，name，FOREIGN KEY（id_A）REFERENCES A（id）DEFERRABLE INITIALLY DEFERRED）; 
 INSERT INTO B（id，id_A，name）VALUES（1,1，Opium）; 
 
 BEGIN; 
从某个地方删除id = 1; 
 INSERT INTO A（id，name）VALUES（1，another Loreum）; 
 COMMIT; 
  

I understand the concepts of PRAGMA foreign_key and of ON DELETE RESTRICT/NO ACTION, but I am facing a different kind of situation.

I need to delete a parent row but keep the child row associated with it. For example:

CREATE TABLE A(id, name);
INSERT INTO A(id, name) VALUES (1, "Loreum");

CREATE TABLE B(id, id_A, name) FOREIGN KEY(id_A) REFERENCES A(id);
INSERT INTO B(id, id_A, name) VALUES (1, 1, "Opium");
DELETE FROM A WHERE id = 1;

I want to achieve this while keeping the child row intact. Is this possible at all?

EDIT

The example above separates my question from this question. Example might help some people, who only understand when there is code.

解决方案

You can do this with a deferred foreign key constraint:

PRAGMA foreign_keys = on;
CREATE TABLE A(id PRIMARY KEY, name);
INSERT INTO A(id, name) VALUES (1, "Loreum");
CREATE TABLE B(id, id_A, name, FOREIGN KEY(id_A) REFERENCES A(id) DEFERRABLE INITIALLY DEFERRED);
INSERT INTO B(id, id_A, name) VALUES (1, 1, "Opium");

BEGIN;
DELETE FROM A WHERE id = 1;
INSERT INTO A(id, name) VALUES (1, "another Loreum");
COMMIT;

这篇关于SQLite - 如何删除父行同时保持子/行的？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！