SQLite的foreign_key_list编译指示的输出 [英] Output of the SQLite's foreign_key_list pragma

问题描述

在以下架构中使用 SQLite3 :

CREATE TABLE Customers(ID INTEGER PRIMARY KEY, Company TEXT NOT NULL UNIQUE, Country TEXT NOT NULL, City TEXT NOT NULL);
CREATE TABLE Orders(ID INTEGER PRIMARY KEY, CustomerID INTEGER NOT NULL, FOREIGN KEY(CustomerID) REFERENCES Customers(ID) ON DELETE RESTRICT ON UPDATE RESTRICT);

并发出以下命令:

PRAGMA foreign_key_list(Orders);

产生以下输出:

0|0|Customers|CustomerID|ID|RESTRICT|RESTRICT|NONE

正如文档所言，该pragma的输出含义无异明显的( Customers -父表， CustomerID -子项， ID -父项， RESTRICT -删除和第二个 RESTRICT -ON UPDATE)，我认为 NONE 核心对应不受支持的MATCH子句.

As the documentation says nothing about the meaning of the output of this pragma, apart from the obvious (Customers - Parent table, CustomerID - Child key, ID - Parent key, RESTRICT - ON DELETE and the second RESTRICT - ON UPDATE) I presume that NONE coresponds to the unsupported MATCH clause.

我自己不知道的事情是前两个零的含义.有人可以告诉我这是什么吗?

The thing which I can't figure out by myself is the meaning of the first two zeros. Could someone tell me what it is?

推荐答案

PRAGMA foreign_key_list()的输出依次包含以下各列-

The output of PRAGMA foreign_key_list() consists of following columns in order -

id，seq，table，from，to，on_update，on_delete，match

因此，在输出中，您得到的前两个0是id和seq的.

So, in the output you got the first two 0s are for id and seq.

以下面的示例在sqlite3 cli中执行的情况为例-

Take below example executed in sqlite3 cli with header and column option on -

CREATE TABLE Test (first INTEGER, second INTEGER, FOREIGN KEY (first) REFERENCES A(a) ON DELETE CASCADE, FOREIGN KEY (second) REFERENCES B(x) ON DELETE CASCADE);
sqlite> 
sqlite> PRAGMA foreign_key_list(Test);
id          seq         table       from        to          on_update   on_delete   match     
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           0           B           second      x           NO ACTION   CASCADE     NONE      
1           0           A           first       a           NO ACTION   CASCADE     NONE 

我知道桌子Test看起来像一场噩梦，但是只是暂时忽略一下可怕的图式.

I know the table Test look like a nightmare, but just ignore the horrible schema for a moment.

您可以看到表Test中有两个外键，因此在PRAGMA foreign_key_list()输出中显示了两个条目.您可以看到id字段值分别为0和1，但seq值都为0.

You can see there are two foreign keys in table Test and so there are two entries shown in PRAGMA foreign_key_list() output. You can see the id fields value are 0 and 1 respectively but the seq values are all 0.

您可能知道，sqlite在外键语句中允许多个列名. 因此，如果您采用下一个示例-

As you may know sqlite allows multiple column names in foreign key statement. So if you take the next example -

sqlite> CREATE TABLE Test2 (first INTEGER, second INTEGER, FOREIGN KEY (first, second) REFERENCES A(a, b) ON DELETE CASCADE);
sqlite> 
sqlite> PRAGMA foreign_key_list(Test2);
id          seq         table       from        to          on_update   on_delete   match     
----------  ----------  ----------  ----------  ----------  ----------  ----------  ----------
0           0           A           first       a           NO ACTION   CASCADE     NONE      
0           1           A           second      b           NO ACTION   CASCADE     NONE  

因此，多列键在输出中导致多个行，其中id与此相同，实际上是一个键.但是seq值不同，因为键中有多个列.

So, the multiple column key results in multiple rows in the output where id is same as this is actually one key. But the seq value differs as there are multiple columns in the key.

这篇关于SQLite的foreign_key_list编译指示的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！