android sqlite新添唯一键constraint错误的解决方法

  今天在做项目,需要在先前的表上面新增一个constraint,然后就用了“alter table mag_content_list add constraint unique(id);”这么一条语句。

  结果,在升级的时候出错了。查询了一下。查到这么一句话“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 column, remove a column, or add or remove constraints from a table.” 

  意思就是说sqlite不支持重命名,删除字段或者添加、删除约束。

  解决的办法就是:

    1、新建一个临时表,当前表数据插入

2、新建一个新表,把临时表数据插入

3、删除临时表和旧的表


以上信息参照了网上的一个文章:http://rimland.blog.163.com/blog/static/883254620129220925454/

内容如下:


Altering SQLite column type and adding PK constraint


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

I've got:

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

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

Answer:

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

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.

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. copying the old table to a temporary table,
  2. creating a new table defined as desired, and
  3. copying the data from the temporary table to the new table.

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;

Notes

  1. I used the table name test_table since SQLite will generate an error if you try to name a table astable.
  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.


 

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值