问题描述
我有下表:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT);
如何在 parent_id 上添加外键约束?假设启用了外键.
How do I add a foreign key constraint on parent_id? Assume foreign keys are enabled.
大多数示例都假设您正在创建表 - 我想将约束添加到现有表中.
Most examples assume you're creating the table - I'd like to add the constraint to an existing one.
推荐答案
你不能.
尽管向表中添加外键的 SQL-92 语法如下所示:
Although the SQL-92 syntax to add a foreign key to your table would be as follows:
ALTER TABLE child ADD CONSTRAINT fk_child_parent
FOREIGN KEY (parent_id)
REFERENCES parent(id);
SQLite 不支持 ALTER TABLE 命令的 ADD CONSTRAINT 变体 (sqlite.org:SQLite 未实现的 SQL 功能).
SQLite doesn't support the ADD CONSTRAINT variant of the ALTER TABLE command (sqlite.org: SQL Features That SQLite Does Not Implement).
因此，在sqlite 3.6.1中添加外键的唯一方法是在CREATE TABLE过程中，如下所示:
Therefore, the only way to add a foreign key in sqlite 3.6.1 is during CREATE TABLE as follows:
CREATE TABLE child (
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT,
FOREIGN KEY (parent_id) REFERENCES parent(id)
);
不幸的是，您必须将现有数据保存到临时表中，删除旧表，使用 FK 约束创建新表，然后从临时表中复制数据.(sqlite.org - 常见问题解答:Q11)
Unfortunately you will have to save the existing data to a temporary table, drop the old table, create the new table with the FK constraint, then copy the data back in from the temporary table. (sqlite.org - FAQ: Q11)
这篇关于如何向现有 SQLite 表添加外键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！