更改 SQLite 列类型并添加 PK 约束 [英] Altering SQLite column type and adding PK constraint

问题描述

如何更改 SQLite 表中列的类型?

How to change the type of a column in a SQLite table?

我有:

    CREATE TABLE table(
        id INTEGER,
        salt TEXT NOT NULL UNIQUE,
        step INT,
        insert_date TIMESTAMP
    );

我想将 salt 的类型改为 TEXT，将 id 的类型改为 INTEGER PRIMARY KEY.

I'd like to change salt's type to just TEXT and id's type to INTEGER PRIMARY KEY.

推荐答案

以下是讨论 ALTER TABLE 命令的 SQLite 手册摘录(请参阅 URL:SQLite 更改表):

Below is an excerpt from the SQLite manual discussing the ALTER TABLE command (see URL: SQLite Alter Table):

SQLite 支持有限的子集更改表.ALTER TABLE 命令在 SQLite 中允许用户重命名表或将新列添加到现有表.这是不可能的重命名列，删除列，或添加或删除约束表.

SQLite supports a limited subset of ALTER TABLE. The ALTER TABLE command in SQLite allows the user to rename a table or to add a new column to an existing table. It is not possible to rename a colum, remove a column, or add or remove constraints from a table.

如手册所述，无法修改列的类型或约束，例如将 NULL 转换为 NOT NULL.但是，

As the manual states, it is not possible to modify a column's type or constraints, such as converting NULL to NOT NULL. However, there is a work around by

1. 将旧表复制到临时表，
2. 创建一个根据需要定义的新表，以及
3. 将数据从临时表复制到新表.

为了在信用到期时给予信用，我从 bitbucket.org 上 hakanw 的 django-email-usernames 项目问题 #1 的讨论中了解到这一点.

To give credit where credit is due, I learned this from the discussion on Issue #1 of hakanw's django-email-usernames project on bitbucket.org.

CREATE TABLE test_table(
    id INTEGER,
    salt TEXT NOT NULL UNIQUE,
    step INT,
    insert_date TIMESTAMP
);

ALTER TABLE test_table RENAME TO test_table_temp;

CREATE TABLE test_table(
    id INTEGER PRIMARY KEY,
    salt TEXT,
    step INT,
    insert_date TIMESTAMP
);

INSERT INTO test_table SELECT * FROM test_table_temp;

DROP TABLE test_table_temp;

备注

1. 我使用了表名 test_table，因为如果您尝试将表命名为 table，SQLite 会产生错误.
2. 如果您的数据不符合新表的约束，INSERT INTO 命令将失败.例如，如果原始 test_table 包含两个具有相同整数的 id 字段，您将收到SQL 错误:PRIMARY KEY 必须是唯一的" 当您执行INSERT INTO test_table SELECT * FROM test_table_temp;"命令时.
3. 对于所有测试，我使用 SQLite 3.4.0 版，作为 Python 2.6.2 的一部分，在我的 13 英寸 Unibody MacBook 和 Mac OS X 10.5.7 上运行.

1. I used the table name test_table since SQLite will generate an error if you try to name a table as table.
2. The INSERT INTO command will fail if your data does not conform to the new table constraints. For instance, if the original test_table contains two id fields with the same integer, you will receive an "SQL error: PRIMARY KEY must be unique" when you execute the "INSERT INTO test_table SELECT * FROM test_table_temp;" command.
3. For all testing, I used SQLite version 3.4.0 as included as part of Python 2.6.2 running on my 13" Unibody MacBook with Mac OS X 10.5.7.

这篇关于更改 SQLite 列类型并添加 PK 约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！