如何将外键添加到现有的SQLite表中? [英] How do I add a foreign key to an existing SQLite table?
问题描述
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT);
如何在 parent_id $ c $上添加外键约束C>?假设外键已启用。
大多数例子都假设你正在创建表 - 我想把这个约束添加到现有的表中。
92语法添加一个外键到你的表中,如下所示:
pre $ alter table child ADD CONSTRAINT fk_child_parent
FOREIGN KEY(parent_id)
REFERENCES parent(id);
SQLite不支持 ADD CONSTRAINT
ALTER TABLE
命令的变体( sqlite.org:SQLite没有实现的SQL特性)。因此,在sqlite 3.6.1中添加外键的唯一方法是在 CREATE TABLE
过程中,如下所示:
<
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT ,
FOREIGN KEY(parent_id)REFERENCES parent(id)
);
不幸的是,您必须将现有数据保存到临时表中,删除旧表,创建新的FK约束表,然后将数据从临时表中复制回来。 ( sqlite.org - FAQ:Q11 )
I have the following table:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT);
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.
You can't.
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 doesn't support the ADD CONSTRAINT
variant of the ALTER TABLE
command (sqlite.org: SQL Features That SQLite Does Not Implement).
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)
);
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表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!