gpt4 book ai didi

sql - SQLITE更新操作

转载 作者:行者123 更新时间:2023-12-03 19:51:22 25 4
gpt4 key购买 nike

基于SQLite foreign key documentation,它应该是创建两个数据库的方式,并且如果父字段被更新,则引用父字段的字段也将被更新。

问题:执行以下步骤后,一切正常,直到最后一条命令为止
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


为什么是这样?

最佳答案

您应该更仔细地阅读fine manual


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支持下编译的。

关于sql - SQLITE更新操作,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/16095746/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com