如何从 sqlite (3.6.21) 表中删除约束?

2021-12-08 00:00:00 constraints sqlite ddl

我有下表:

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

如何删除约束?

推荐答案

SQLite 不(截至本答案)支持 alter table drop constraint 命令.可以在此处查看允许的语法.您需要创建一个没有约束的新表，传输数据，然后删除旧表.

SQLite does not (as of this answer) support the alter table drop constraint command. The allowed syntax can be seen here. You will need to create a new table without a constraint, transfer the data, then delete the old table.

我认为类似以下内容应该有效:

I think something like the following should work:

CREATE TABLE child2 ( 
    id          INTEGER PRIMARY KEY, 
    parent_id   INTEGER,
    description TEXT
);
INSERT INTO child2 (id, parent_id, description)
   SELECT id, parent_id, description FROM CHILD;
DROP TABLE child;
ALTER TABLE child2 RENAME TO child;

请注意，insert into 可能会简化为不 使用显式列名，但我保留了这种方式，以防您也想更改结构.

Note that the insert into could probably be simplified to not use explicit column names but I've left it that way in case you want to change the structure as well.

例如，如果您要删除parent_id 列上的约束，那么将其保留在那里的用途很可疑.在这种情况下，您可以将数据传输修改为:

For example, if you're removing the constraint on the parent_id column, it's of dubious use to keep it there at all. In that case, you could modify the data transfer to:

CREATE TABLE child2 (id INTEGER PRIMARY KEY, description TEXT);
INSERT INTO child2 (id, description) SELECT id, description FROM CHILD;

相关文章