android - SQLite 改变列以删除约束

Question

我有一个带有 NOT NULL 约束的列。我需要更改数据库以删除约束。但是当我尝试时，我从 android 中得到了一个重复的列异常

db.execSQL("ALTER TABLE " + AnimalContract.DogEntry.TABLE_NAME
+ " ADD COLUMN " + AnimalContract.DogEntry.COLUMN_NAME+" TEXT DEFAULT NULL");

我也试过没有默认

约束是UNIQUE NOT NULL

Answer 1

在 SQLite 中没有直接的方法来ALTER COLUMN。

我相信你唯一的选择是:

• 将表重命名为临时名称
• 创建一个没有 NOT NULL 约束的新表
• 将旧表的内容复制到新表
• 删除旧表

This other Stackoverflow answer详细解释过程

此外，

修改表格中的列

您不能使用 ALTER TABLE 语句修改 SQLite 中的列。相反，您需要重命名表、创建新表并将数据复制到新表中。

语法

The syntax to MODIFY A COLUMN in a table in SQLite is:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table1 RENAME TO _table1_old;

CREATE TABLE table1 (
( column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

INSERT INTO table1 (column1, column2)
 SELECT column1, column2
  FROM _table1_old;

COMMIT;

PRAGMA foreign_keys=on;

Example

Let's look at an example that shows how to modify a column in a SQLite table.

For example, if we had an employees table that had a column called last_name that was defined as a CHAR datatype:

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name CHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

And we wanted to change the datatype of the last_name field to VARCHAR, we could do the following:

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE employees RENAME TO _employees_old;

CREATE TABLE employees
( employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR,
  hire_date DATE
);

INSERT INTO employees (employee_id, last_name, first_name, hire_date)
  SELECT employee_id, last_name, first_name, hire_date
  FROM _employees_old;

COMMIT;

PRAGMA foreign_keys=on;

This example will rename our existing employees table to _employees_old. Then it will create the new employees table with the last_name field defined as a VARCHAR datatype. Then it will insert all of the data from the _employees_old table into the employees table.