根据SQLite外键文档,它应该是创建两个数据库的方式,并且如果父字段更新,引用父字段的字段也会更新。
问题:一旦我按照下面的步骤,一切都工作正常,直到最后一个命令SELECT * FROM track;
,因为结果仍然与以下相同,因此它应该更改为最后显示的结果。
trackid trackname trackartist
------- ----------------- -----------
11 That's Amore 1
12 Christmas Blues 1
13 My Way 2
编码:-- Database schema
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
);
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER REFERENCES artist(artistid) ON UPDATE CASCADE
);
sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
1 Dean Martin
2 Frank Sinatra
sqlite> SELECT * FROM track;
trackid trackname trackartist
------- ----------------- -----------
11 That's Amore 1
12 Christmas Blues 1
13 My Way 2
sqlite> -- Update the artistid column of the artist record for "Dean Martin".
sqlite> -- Normally, this would raise a constraint, as it would orphan the two
sqlite> -- dependent records in the track table. However, the ON UPDATE CASCADE clause
sqlite> -- attached to the foreign key definition causes the update to "cascade"
sqlite> -- to the child table, preventing the foreign key constraint violation.
sqlite> UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';
sqlite> SELECT * FROM artist;
artistid artistname
-------- -----------------
2 Frank Sinatra
100 Dean Martin
sqlite> SELECT * FROM track;
trackid trackname trackartist
------- ----------------- -----------
11 That's Amore 100
12 Christmas Blues 100
13 My Way 2
为什么会这样?
你应该更仔细地阅读精细的手册:
2。启用外键支持
[…]
假设库是在启用外键约束的情况下编译的,那么应用程序仍然必须在运行时使用PRAGMA foreign_keys命令启用它。例如:sqlite> PRAGMA foreign_keys = ON;
默认情况下外键约束是禁用的(为了向后兼容),因此必须为每个数据库连接单独启用。
所以如果你这样写:
sqlite> PRAGMA foreign_keys = ON;
sqlite> UPDATE artist SET artistid = 100 WHERE artistname = 'Dean Martin';
,那么你会在track
中看到你所期望的100。当然,这假定您的SQLite是在FK支持下编译的。