我有下表:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT);
如何在parent_id
上添加外键约束?假设启用了外键。
大多数示例都假设您正在创建表-我想将约束添加到现有的表中。
发布于 2014-05-10 05:12:20
如果更改table并添加使用约束的列,则可以添加约束。
首先,创建不带parent_id的表:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
description TEXT);
然后,alter table:
ALTER TABLE child ADD COLUMN parent_id INTEGER REFERENCES parent(id);
发布于 2017-12-29 11:07:06
是的,您可以,而无需添加新列。为了避免损坏数据库,您必须小心正确地执行此操作,因此在尝试此操作之前,您应该完全备份数据库。
对于您的特定示例:
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';
发布于 2021-05-01 17:20:30
正如@Daniel Vassallo所说,你不能这样做。您必须使用的代码如下所示:
给出表:
CREATE TABLE child(
id INTEGER PRIMARY KEY,
parent_id INTEGER,
description TEXT);
我假设您想要添加以下Foreignk密钥:
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;
https://stackoverflow.com/questions/1884818
复制相似问题