SQL 如何向现有 SQLite 表添加外键？

How do I add a foreign key to an existing SQLite table?

提问by Dane O'Connor

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.

如何在 上添加外键约束parent_id？假设启用了外键。

Most examples assume you're creating the table - I'd like to add the constraint to an existing one.

大多数示例都假设您正在创建表 - 我想将约束添加到现有表中。

回答by Daniel Vassallo

You can't.

你不能。

Although the SQL-92 syntax to add a foreign key to your table would be as follows:

尽管向表中添加外键的 SQL-92 语法如下所示：

ALTER TABLE child ADD CONSTRAINT fk_child_parent
                  FOREIGN KEY (parent_id) 
                  REFERENCES parent(id);

SQLite doesn't supportthe ADD CONSTRAINTvariant of the ALTER TABLEcommand (sqlite.org: SQL Features That SQLite Does Not Implement).

SQLite 不支持该命令的ADD CONSTRAINT变体ALTER TABLE（sqlite.org：SQLite 未实现的 SQL 功能）。

Therefore, the only way to add a foreign key in sqlite 3.6.1 is during CREATE TABLEas follows:

因此，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)
);

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)

不幸的是，您必须将现有数据保存到临时表中，删除旧表，使用 FK 约束创建新表，然后从临时表中复制数据。（sqlite.org - 常见问题解答：Q11）

回答by Jorge Novaes

You can add the constraint if you alter table and add the column that uses the constraint.

如果更改表并添加使用约束的列，则可以添加约束。

First, create table without the parent_id:

首先，创建没有 parent_id 的表：

CREATE TABLE child( 
  id INTEGER PRIMARY KEY,  
  description TEXT);

Then, alter table:

然后，更改表：

ALTER TABLE child ADD COLUMN parent_id INTEGER REFERENCES parent(id);

回答by situee

Please check https://www.sqlite.org/lang_altertable.html#otheralter

请检查https://www.sqlite.org/lang_altertable.html#otheralter

The only schema altering commands directly supported by SQLite are the "rename table" and "add column" commands shown above. However, applications can make other arbitrary changes to the format of a table using a simple sequence of operations. The steps to make arbitrary changes to the schema design of some table X are as follows:

1. If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF.
2. Start a transaction.
3. Remember the format of all indexes and triggers associated with table X. This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.
4. Use CREATE TABLE to construct a new table "new_X" that is in the desired revised format of table X. Make sure that the name "new_X" does not collide with any existing table name, of course.
5. Transfer content from X into new_X using a statement like: INSERT INTO new_X SELECT ... FROM X.
6. Drop the old table X: DROP TABLE X.
7. Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.
8. Use CREATE INDEX and CREATE TRIGGER to reconstruct indexes and triggers associated with table X. Perhaps use the old format of the triggers and indexes saved from step 3 above as a guide, making changes as appropriate for the alteration.
9. If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.
10. If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints.
11. Commit the transaction started in step 2.
12. If foreign keys constraints were originally enabled, reenable them now.

The procedure above is completely general and will work even if the schema change causes the information stored in the table to change. So the full procedure above is appropriate for dropping a column, changing the order of columns, adding or removing a UNIQUE constraint or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints, or changing the datatype for a column, for example.

SQLite 直接支持的唯一模式更改命令是上面显示的“重命名表”和“添加列”命令。但是，应用程序可以使用简单的操作序列对表的格式进行其他任意更改。对某表X的schema设计进行任意修改的步骤如下：

1. 如果启用了外键约束，请使用 PRAGMA foreign_keys=OFF 禁用它们。
2. 开始交易。
3. 记住与表 X 关联的所有索引和触发器的格式。在下面的步骤 8 中将需要此信息。一种方法是运行如下查询：SELECT type, sql FROM sqlite_master WHERE tbl_name='X'。
4. 使用 CREATE TABLE 构造一个新表“new_X”，该表采用表 X 的所需修订格式。当然，确保名称“new_X”不会与任何现有表名冲突。
5. 使用如下语句将内容从 X 传输到 new_X：INSERT INTO new_X SELECT ... FROM X。
6. 删除旧表 X：DROP TABLE X。
7. 使用以下命令将 new_X 的名称更改为 X：ALTER TABLE new_X RENAME TO X。
8. 使用 CREATE INDEX 和 CREATE TRIGGER 重建与表 X 关联的索引和触发器。也许使用从上面第 3 步中保存的触发器和索引的旧格式作为指导，根据更改进行适当的更改。
9. 如果任何视图以受模式更改影响的方式引用表 X，则使用 DROP VIEW 删除这些视图，并使用 CREATE VIEW 使用任何必要的更改重新创建它们以适应模式更改。
10. 如果最初启用了外键约束，则运行 PRAGMA foreign_key_check 以验证架构更改没有破坏任何外键约束。
11. 提交在步骤 2 中开始的事务。
12. 如果最初启用外键约束，现在重新启用它们。

上面的过程是完全通用的，即使架构更改导致存储在表中的信息发生更改也能正常工作。因此，上述完整过程适用于删除列、更改列的顺序、添加或删除 UNIQUE 约束或 PRIMARY KEY、添加 CHECK 或 FOREIGN KEY 或 NOT NULL 约束，或者更改列的数据类型等。

回答by mwag

Yes, you can, without adding a new column. You have to be careful to do it correctly in order to avoid corrupting the database, so you should completely back up your database before trying this.

是的，您可以，无需添加新列。您必须小心正确地执行此操作以避免损坏数据库，因此您应该在尝试此操作之前完全备份您的数据库。

for your specific example:

对于您的具体示例：

CREATE TABLE child(
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  description TEXT
);

--- create the table we want to reference
create table parent(id integer not null primary key);

--- now we add the foreign key
pragma writable_schema=1;
update SQLITE_MASTER set sql = replace(sql, 'description TEXT)',
    'description TEXT, foreign key (parent_id) references parent(id))'
) where name = 'child' and type = 'table';

--- test the foreign key
pragma foreign_keys=on;
insert into parent values(1);
insert into child values(1, 1, 'hi'); --- works
insert into child values(2, 2, 'bye'); --- fails, foreign key violation

or more generally:

或更一般地说：

pragma writable_schema=1;

// replace the entire table's SQL definition, where new_sql_definition contains the foreign key clause you want to add
UPDATE SQLITE_MASTER SET SQL = new_sql_definition where name = 'child' and type = 'table';

// alternatively, you might find it easier to use replace, if you can match the exact end of the sql definition
// for example, if the last column was my_last_column integer not null:
UPDATE SQLITE_MASTER SET SQL = replace(sql, 'my_last_column integer not null', 'my_last_column integer not null, foreign key (col1, col2) references other_table(col1, col2)') where name = 'child' and type = 'table';

pragma writable_schema=0;

Either way, you'll probably want to first see what the SQL definition is before you make any changes:

无论哪种方式，您都可能希望在进行任何更改之前先查看 SQL 定义是什么：

select sql from SQLITE_MASTER where name = 'child' and type = 'table';

If you use the replace() approach, you may find it helpful, before executing, to first test your replace() command by running:

如果您使用 replace() 方法，您可能会发现在执行之前先通过运行以下命令来测试您的 replace() 命令会很有帮助：

select replace(sql, ...) from SQLITE_MASTER where name = 'child' and type = 'table';

回答by Baso

If you are using the Firefox add-on sqlite-manager you can do the following:

如果您使用的是 Firefox 插件 sqlite-manager，您可以执行以下操作：

Instead of dropping and creating the table again one can just modify it like this.

可以像这样修改它，而不是再次删除和创建表。

In the Columns text box, right click on the last column name listed to bring up the context menu and select Edit Column. Note that if the last column in the TABLE definition is the PRIMARY KEY then it will be necessary to first add a new column and then edit the column type of the new column in order to add the FOREIGN KEY definition. Within the Column Type box , append a comma and the

在“列”文本框中，右键单击列出的最后一个列名称以调出上下文菜单并选择“编辑列”。请注意，如果 TABLE 定义中的最后一列是 PRIMARY KEY，则需要先添加一个新列，然后编辑新列的列类型以添加​​ FOREIGN KEY 定义。在“列类型”框中，附加一个逗号和

FOREIGN KEY (parent_id) REFERENCES parent(id)

definition after data type. Click on the Change button and then click the Yes button on the Dangerous Operation dialog box.

数据类型后定义。单击“更改”按钮，然后单击“危险操作”对话框中的“是”按钮。

Reference: Sqlite Manager

参考： Sqlite 管理器

回答by Jamshy EK

You can try this:

你可以试试这个：

ALTER TABLE [Child] ADD COLUMN column_name INTEGER REFERENCES parent_table_name(column_id);

回答by saeed khalafinejad

Basically you cannot but you can bypass the situation.

基本上你不能，但你可以绕过这种情况。

The correct way to add the foreign key constraint to an existing table is the following command.

将外键约束添加到现有表的正确方法是以下命令。

db.execSQL("alter table child add column newCol integer REFERENCES parent(parent_Id)");

Then copy the parent_Iddata to the newColand then delete the Parent_Idcolumn. Hence, no need for temporary table.

然后将parent_Id数据复制到newCol 中，然后删除Parent_Id列。因此，不需要临时表。

回答by Tariq Nawaz Khan

First add a column in child table Cidas intthen alter tablewith the code below. This way you can add the foreign key Cidas the primary key of parent table and use it as the foreign key in child table ... hope it will help you as it is good for me:

首先在添加子表列Cid如int然后alter table用下面的代码。这样您就可以将外键添加Cid为父表的主键，并将其用作子表中的外键......希望它对您有帮助，因为它对我有好处：

ALTER TABLE [child] 
  ADD CONSTRAINT [CId] 
  FOREIGN KEY ([CId]) 
  REFERENCES [Parent]([CId]) 
  ON DELETE CASCADE ON UPDATE NO ACTION;
GO