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

发布于 2024-08-15 00:51:17 字数 219 浏览 5 评论 0原文

我有下表：

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

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

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

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

や莫失莫忘 2024-08-22 00:51:17

你不能。

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

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

SQLite 不支持 ADD CONSTRAINT< ALTER TABLE 命令的 /code> 变体（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 - 常见问题解答：Q11）

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)

回复收藏 0 原文

绾颜 2024-08-22 00:51:17

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

首先，创建没有parent_id的表：

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

然后，更改表：

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

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

First, create table without the 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);

回复收藏 0 原文

小镇女孩 2024-08-22 00:51:17

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

对于您的具体示例：

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

或者更一般地说：

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;

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

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

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

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

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:

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:

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

回复收藏 0 原文

许久 2024-08-22 00:51:17

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

SQLite 直接支持的唯一模式更改命令是
上面显示的“重命名表”和“添加列”命令。然而，
应用程序可以对表的格式进行其他任意更改
使用简单的操作序列。任意制作的步骤
部分表X的schema设计变化如下：
如果启用了外键约束，请使用 PRAGMA 禁用它们
foreign_keys=关闭。
开始交易。
记住与以下内容关联的所有索引和触发器的格式
表 X。下面的步骤 8 中将需要此信息。一种方法是
执行此操作是运行如下查询：SELECT type, sql FROM
sqlite_master WHERE tbl_name='X'。
使用 CREATE TABLE 构造一个新表“new_X”，该表位于
表 X 所需的修订格式。确保名称“new_X”
当然，不会与任何现有的表名称冲突。
使用如下语句将内容从 X 传输到 new_X：INSERT
INTO new_X SELECT ... FROM X。
删除旧表 X：DROP TABLE X。
使用以下命令将 new_X 的名称更改为 X：ALTER TABLE new_X RENAME TO X。
使用 CREATE INDEX 和 CREATE TRIGGER 重建索引并
与表 X 关联的触发器。也许使用旧格式
从上面的步骤 3 保存的触发器和索引作为指导，使得
根据变更进行适当的更改。
如果任何视图以受
架构更改，然后使用 DROP VIEW 删除这些视图并重新创建
他们进行任何必要的更改以适应模式
使用 CREATE VIEW 进行更改。
如果最初启用了外键约束，则运行 PRAGMA
foreign_key_check 验证架构更改没有中断
任何外键约束。
提交第 2 步中开始的事务。
如果最初启用了外键约束，请重新启用它们
现在。
上面的过程是完全通用的，即使
架构更改会导致表中存储的信息发生更改。所以
上面的完整过程适用于删除列，
更改列的顺序、添加或删除 UNIQUE 约束
或 PRIMARY KEY，添加 CHECK 或 FOREIGN KEY 或 NOT NULL 约束，
例如，或更改列的数据类型。

Please check 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:
If foreign key constraints are enabled, disable them using PRAGMA
foreign_keys=OFF.
Start a transaction.
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'.
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.
Transfer content from X into new_X using a statement like: INSERT
INTO new_X SELECT ... FROM X.
Drop the old table X: DROP TABLE X.
Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.
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.
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.
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.
Commit the transaction started in step 2.
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.

回复收藏 0 原文

别忘他 2024-08-22 00:51:17

正如 @Daniel Vassallo 所说，你做不到。您必须使用的代码如下所示：

给定表：

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

我假设您要添加以下外键：

FOREIGN KEY (parent_id) REFERENCES parent(id);

因此我将基于该表创建一个临时表，然后我将创建一个新表作为第一个一个，但使用外键，最后我将临时表的数据添加到其中：

CREATE TEMPORARY TABLE temp AS
SELECT 
    id,
    parent_id,
    description
FROM child;

DROP TABLE child;

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

INSERT INTO child
 (  id,
    parent_id,
    description)
SELECT
    id,
    parent_id,
    description
FROM temp;

As @Daniel Vassallo has said, you can't do it. The code that you have to use is something like this:

Given the table:

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

I assume that you want to add the following Foreignk Key:

FOREIGN KEY (parent_id) REFERENCES parent(id);

So I would create a temporary table based on that table, then I would create a new table as the first one but with the Foreign Key and finally I would add the data of the temporary table to it:

CREATE TEMPORARY TABLE temp AS
SELECT 
    id,
    parent_id,
    description
FROM child;

DROP TABLE child;

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

INSERT INTO child
 (  id,
    parent_id,
    description)
SELECT
    id,
    parent_id,
    description
FROM temp;

回复收藏 0 原文

度的依靠╰つ 2024-08-22 00:51:17

你可以试试这个：

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

You can try this:

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

回复收藏 0 原文

对你而言 2024-08-22 00:51:17

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

无需删除并再次创建表，只需像这样修改它即可。

在“列”文本框中，右键单击列出的最后一个列名称以显示上下文菜单，然后选择“编辑列”。
请注意，如果表定义中的最后一列是主键，则需要首先添加新列，然后编辑新列的列类型，以便添加外键定义。
在“列类型”框中，

FOREIGN KEY (parent_id) REFERENCES parent(id)

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

参考：
Sqlite 管理器

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

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

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

回复收藏 0 原文

简单 2024-08-22 00:51:17

如果您使用 SQLite 的 Db Browser，那么修改表将很容易。您可以在现有表中添加外键，而无需编写查询。

在数据库浏览器中打开数据库，
只需右键单击表并单击修改，
滚动到外键列，
双击要更改的字段，
然后选择表及其字段并单击确定。

就是这样。您已成功在现有表中添加外键。

回复收藏 0 原文

动次打次papapa 2024-08-22 00:51:17

创建现有 SQLLite 表的外键：

对于 SQL LITE 没有直接的方法可以做到这一点。运行以下查询以使用外键重新创建 STUDENTS 表。
创建初始 STUDENTS 表并向该表插入数据后运行查询。

CREATE TABLE    STUDENTS    (       
    STUDENT_ID  INT NOT NULL,   
    FIRST_NAME  VARCHAR(50) NOT NULL,   
    LAST_NAME   VARCHAR(50) NOT NULL,   
    CITY    VARCHAR(50) DEFAULT NULL,   
    BADGE_NO    INT DEFAULT NULL
    PRIMARY KEY(STUDENT_ID) 
);

将数据插入 STUDENTS 表。

然后添加外键：将 BADGE_NO 作为同一 STUDENTS 表的外键，

BEGIN;
CREATE TABLE STUDENTS_new (
    STUDENT_ID  INT NOT NULL,   
    FIRST_NAME  VARCHAR(50) NOT NULL,   
    LAST_NAME   VARCHAR(50) NOT NULL,   
    CITY    VARCHAR(50) DEFAULT NULL,   
    BADGE_NO    INT DEFAULT NULL,
    PRIMARY KEY(STUDENT_ID) ,
    FOREIGN KEY(BADGE_NO) REFERENCES STUDENTS(STUDENT_ID)   
);
INSERT INTO STUDENTS_new SELECT * FROM STUDENTS;
DROP TABLE STUDENTS;
ALTER TABLE STUDENTS_new RENAME TO STUDENTS;
COMMIT;

我们也可以添加任何其他表中的外键。

Create a foreign key to the existing SQLLite table:

There is no direct way to do that for SQL LITE. Run the below query to recreate STUDENTS table with foreign keys.
Run the query after creating initial STUDENTS table and inserting data into the table.

CREATE TABLE    STUDENTS    (       
    STUDENT_ID  INT NOT NULL,   
    FIRST_NAME  VARCHAR(50) NOT NULL,   
    LAST_NAME   VARCHAR(50) NOT NULL,   
    CITY    VARCHAR(50) DEFAULT NULL,   
    BADGE_NO    INT DEFAULT NULL
    PRIMARY KEY(STUDENT_ID) 
);

Insert data into STUDENTS table.

Then Add FOREIGN KEY : making BADGE_NO as the foreign key of same STUDENTS table

BEGIN;
CREATE TABLE STUDENTS_new (
    STUDENT_ID  INT NOT NULL,   
    FIRST_NAME  VARCHAR(50) NOT NULL,   
    LAST_NAME   VARCHAR(50) NOT NULL,   
    CITY    VARCHAR(50) DEFAULT NULL,   
    BADGE_NO    INT DEFAULT NULL,
    PRIMARY KEY(STUDENT_ID) ,
    FOREIGN KEY(BADGE_NO) REFERENCES STUDENTS(STUDENT_ID)   
);
INSERT INTO STUDENTS_new SELECT * FROM STUDENTS;
DROP TABLE STUDENTS;
ALTER TABLE STUDENTS_new RENAME TO STUDENTS;
COMMIT;

we can add the foreign key from any other table as well.

回复收藏 0 原文

秋千易 2024-08-22 00:51:17

如果其他人需要有关 SQLiteStudio 的信息，您可以通过它的 GUI 轻松完成。

双击该列，然后双击外键行，然后勾选外键并单击配置。您可以添加参考列，然后在每个窗口中单击“确定”。

最后单击绿色勾号以提交结构中的更改。

请注意，这些步骤会创建删除表并重新创建它的 SQL 脚本！

从数据库备份您的数据。

回复收藏 0 原文

疏忽 2024-08-22 00:51:17

只是为了直观地完成 @Gaurav Ganani 的解释，这对我来说效果很好（我希望它适用于那些使用 DB Browser for SQLite 的人），让我将这张图片粘贴到这里：

回复收藏 0 原文

树深时见影 2024-08-22 00:51:17

打开数据库，右键单击表，选择修改数据库。
在打开的窗口中，选择您想要创建外键的列，然后滚动到右侧，您将找到一个写有外键的列，然后轻松选择您引用的表以及该列。

回复收藏 0 原文

一城柳絮吹成雪 2024-08-22 00:51:17

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

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

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

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

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_Id data to the newCol and then delete the Parent_Id column.
Hence, no need for temporary table.

回复收藏 0 原文

聽兲甴掵 2024-08-22 00:51:17

首先在子表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

First add a column in child table Cid as int then alter table with the code below. This way you can add the foreign key Cid as 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:

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

回复收藏 0 原文

~没有更多了~

关于作者

﹏雨一样淡蓝的深情

暂无简介

0 文章

0 评论

21 人气

关注发私信

友情链接

文江博客

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（14）

关于作者

相关话题

热门标签

推荐作者

烙印

singlesman

给自己一个微笑

独孤求败

晨钟暮鼓

我是自愿种绣球花的

友情链接

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（14）

关于作者

相关话题

热门标签

推荐作者

烙印

singlesman

给自己一个微笑

独孤求败

晨钟暮鼓

我是自愿种绣球花的

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。