是什么导致这个 sqlite 外键不匹配?

2022-01-20 00:00:00 foreign-keys sqlite

我已经查看了这个问题，并认为我已经找到了答案 - 但它看起来不对我.

I already checked out this question, and thought I had the answer - but then it didn't look right to me.

我有以下精简示例:

CREATE TABLE pipelines (                                                        
        name VARCHAR NOT NULL,                                                  
        owner VARCHAR NOT NULL,                                                 
        description VARCHAR,                                                    
        PRIMARY KEY (name, owner),                                              
        FOREIGN KEY(owner) REFERENCES user (id)                                 
);                                                                              
CREATE TABLE tasks (                                                            
        id INTEGER NOT NULL,                                                    
        title VARCHAR,                                                          
        pipeline VARCHAR,                                                       
        owner VARCHAR,                                                          
        PRIMARY KEY (id),                                                       
        FOREIGN KEY(pipeline) REFERENCES pipelines (name),                      
        FOREIGN KEY(owner) REFERENCES pipelines (owner)                         
);                                                                              
CREATE TABLE user (                                                           
        id VARCHAR NOT NULL,                                                    
        name VARCHAR,                                                           
        password VARCHAR,                                                       
        PRIMARY KEY (id)                                                        
);                                                                              
pragma foreign_keys=on;                                                         

insert into user values ('wayne', '', '');                                    
insert into pipelines values ('pipey', 'wayne', '');                            
insert into tasks values (1, 'hello', 'pipey', 'wayne');

执行此代码时，它会退出:

When executing this code, it bails out:

$ sqlite3 foo.sq3 '.read mismatch.sql'    
Error: near line 27: foreign key mismatch

通过我引用的问题中的列表:

Through the list in the question I cited:

  • 父表(用户)存在.
  • 存在父列(名称、所有者)
  • 实际上，父列是主键(我认为最初可能是它)
  • 子表引用父表中的所有主键列

那么到底是什么导致了这个错误呢?

So what in the world could be causing this error?

推荐答案

文档说:

通常，外键约束的父键是父表的主键.如果它们不是主键，则父键列必须共同受 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.

在 pipelines 表中，name 和 owner 列本身都不是唯一的.

In the pipelines table, neither the name nor the owner columns are, by themselves, unique.

我猜你实际上想在 tasks 表中有一个两列外键:

I guess you actually want to have a two-column foreign key in the tasks table:

FOREIGN KEY(pipeline, owner) REFERENCES pipelines(name, owner)

相关文章