sqlite3“外键约束失败” [英] sqlite3 "foreign key constraint failed"

问题描述

我已经建立了两个表：

pre $ code CREATE TABLE

id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT，
name TEXT
）;

CREATE TABLE b
（
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT，
id2 INTEGER，
book TEXT，
FOREIGN KEY（id2 ）参考A（id）
）;

将数据插入 A 后，看起来像这样：

  1约翰
 
 2艾米
 
 3彼得
  

将数据插入 B 后，看起来像这样：

  1 1指环王
 
 2 1捕捉22 
 
 3 2所有恐惧之和
 
 4 3寻找红色十月
  

然后执行下面的语句：

 从where id = 1删除; 
  

我得到以下内容：错误：外键约束失败 code

然后重新启动 sqlite3 然后再试一次，但是这次我首先输入： p>



  PRAGMA foreign_keys = 1; 
  

它仍然不起作用......

<表B 具有外键引用表的主键值的行您试图删除的行，删除它会违反数据库的完整性。

您可以包含 ON在外键定义中删除CASCADE 。因此，从表A 删除条目时，链接到删除行的表B 中的任何条目也将被删除。不知道这是否适合您的应用程序。

I've set up two tables:

CREATE TABLE A
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name TEXT
 );

CREATE TABLE B
(
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    id2 INTEGER,
    book TEXT,
    FOREIGN KEY(id2) REFERENCES A(id)
);

After I insert data into A, it looks like this:

1    John

2    Amy

3    Peter

After I insert data into B, it looks like this:

1     1     Lord of the Rings

2     1     Catch 22

3     2     Sum of All Fears

4     3     Hunt for Red October

I then execute the following statement:

delete from a where id=1;

I get the following: "Error: foreign key constraint failed"

I then restart sqlite3 and try again but this time I enter this first:

PRAGMA foreign_keys = 1;

it still doesn't work......

解决方案

Table B has rows whose foreign key references the primary key value of the Table A row you are trying to delete so deleting it would violate the integrity of your database.

You could include ON DELETE CASCADE in your foreign key definition. With that, when you delete an entry from Table A, any entries in Table B linked to the deleted row would also be deleted. Don't know if that's appropriate for your application.

这篇关于sqlite3“外键约束失败”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！