sqlalchemy外键仅适用于主键? [英] sqlalchemy foreign keys works only with primary key?

问题描述

表和外键创建正常，但工作不正常:

Tables and foreign keys are creating ok, but working not ok:

SQLite version 3.7.9 2011-11-01 00:52:41
sqlite> pragma foreign_keys=on;
sqlite> 
sqlite> CREATE TABLE t1(id int primary key, uuid varchar(36));
sqlite> CREATE TABLE t2(id int primary key, t1_uuid varchar(36), FOREIGN KEY (t1_uuid)    REFERENCES t1(uuid));
sqlite> 
sqlite> INSERT INTO t1(uuid) values ("uuid-1");
sqlite> INSERT INTO t2(t1_uuid) values ("uuid-1");
Error: foreign key mismatch

但是，如果我使用t1(uuid)主键，则所有功能都将按预期工作:

But if i make t1(uuid) primary key, all works as expected:

sqlite> pragma foreign_keys=off;
sqlite> DROP TABLE t1;
sqlite> DROP TABLE t2;
sqlite> pragma foreign_keys=on;
sqlite> CREATE TABLE t1(id int, uuid varchar(36) primary key);
sqlite> CREATE TABLE t2(id int primary key, t1_uuid varchar(36), FOREIGN KEY (t1_uuid) REFERENCES t1(uuid));
sqlite> INSERT INTO t1(uuid) values ("uuid-1");
sqlite> INSERT INTO t2(t1_uuid) values ("uuid-1");

创建无所作为的索引:

sqlite> pragma foreign_keys=on;
sqlite> CREATE TABLE t1(id int primary key, uuid varchar(36));
sqlite> CREATE INDEX uuindex ON t1(uuid);
sqlite> CREATE TABLE t2(id int primary key, t1_uuid varchar(36), FOREIGN KEY (t1_uuid) REFERENCES t1(uuid));
sqlite> INSERT INTO t1(uuid) values ("uuid-1");
sqlite> INSERT INTO t2(t1_uuid) values ("uuid-1");
Error: foreign key mismatch

推荐答案

文档说:

通常，外键约束的父键是父表的主键.如果它们不是主键，则父键列必须共同受到UNIQUE约束或具有UNIQUE索引.

Usually, the parent key of a foreign key constraint is the primary key of the parent table. If they are not the primary key, then the parent key columns must be collectively subject to a UNIQUE constraint or have a UNIQUE index.

这篇关于sqlalchemy外键仅适用于主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！