sqlite3外键约束引用的列要有unique约束

2020年10月23日 / 13次阅读 / Last Modified 2020年10月28日
SQLite

我认为SQL语言的外键约束是非常好的一个feature,用sqlite3时,必须要打开。

外键约束引用的列,它一般情况下是table的主键,也可以是其它非主键的列,但是这个列必须要有unique属性。如果没有unique属性,sqlite3会提示 foreign key mismatch 的错误。

sqlite> pragma foreign_keys=on;
sqlite>
sqlite> create table aa(id integer primary key, content text);
sqlite> insert into aa values (1,1);
sqlite> insert into aa values (2,1);
sqlite> insert into aa values (3,1);
sqlite> .mode column
sqlite> select * from aa;
id  content
--  -------
1   1
2   1
3   1
sqlite> create table bb(content text references aa(content));
sqlite> insert into bb values (1);
Error: foreign key mismatch - "bb" referencing "aa"
sqlite> insert into bb values (2);
Error: foreign key mismatch - "bb" referencing "aa"
sqlite> insert into bb values (3);
Error: foreign key mismatch - "bb" referencing "aa"
sqlite> insert into bb values ('1');
Error: foreign key mismatch - "bb" referencing "aa"
sqlite> insert into bb values ("1");
Error: foreign key mismatch - "bb" referencing "aa"

以上是这个错误的演示。

aa表的content列,由于没有unique约束,导致bb表无法insert数据。

在创建bb表的时候,aa表里已经有相同的content row,但是create table不会检查,这一步没有错误。直到insert into bb的时候,sqlite3提示 foreign key mismatch。

解决这个问题,就要在创建aa表的时候,给content列加上unique约束。

另一种subtle的情况,aa表的content列没有unique约束,但是实际情况是unique的(由程序逻辑保证),这时也一样会报错:

sqlite> create table c1(id integer primary key, content text);
sqlite> insert into c1 values (1,1);
sqlite> insert into c1 values (2,2);
sqlite> select * from c1;
id  content
--  -------
1   1
2   2
sqlite> create table c2(content text references c1(content));
sqlite> insert into c2 values (1);
Error: foreign key mismatch - "c2" referencing "c1"
sqlite> insert into c2 values (2);
Error: foreign key mismatch - "c2" referencing "c1"
sqlite>
sqlite> insert into c2 values (3);
Error: foreign key mismatch - "c2" referencing "c1"

表c1的content列没有unique约束,但是实际情况是unique的,一样报错。

可以理解为:aa表的content列虽然现在的数据满足unique,但是不保证以后的insert into操作,不会导致重复数据!

-- EOF --

本文链接:https://www.pynote.net/archives/2633

留言区

《sqlite3外键约束引用的列要有unique约束》有3条留言

电子邮件地址不会被公开。 必填项已用*标注

  • 麦新杰

    foreign key 只能应用存在 unique 约束的column,有 unique,SQLite3就会自动为其创建index,进一步带来性能上的一点开销!那些互联网公司仅通过程序逻辑来保证一致和可靠,也是有道理的。 [回复]

  • 麦新杰

    The REFERENCES constraint indicates that this column is a foreign key. The constraint indicates which table is referenced and, optionally, which column. If no column is indicated, the foreign key will reference the primary key (meaning the column reference used in the prior example is not required, since employee_id is the primary key of the employee table). The vast majority of foreign keys will reference a primary key, but if a column other than the primary key is used, that column must have a UNIQUE constraint, or it must have a single-column UNIQUE index. [回复]

  • 麦新杰

    可以通过程序逻辑,来自己实现没有unique约束时的“约束”,让数据库来做约束,有一定效率的损失。 [回复]


前一篇:
后一篇:

More


©Copyright 麦新杰 Since 2019 Python笔记

go to top