android sqlite新添唯一键constraint错误的解决方法
来源:互联网 发布:手机淘宝版本5.9.8 编辑:程序博客网 时间:2024/06/09 08:28
今天在做项目,需要在先前的表上面新增一个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
- copying the old table to a temporary table,
- creating a new table defined as desired, and
- 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
- I used the table name
test_table
since SQLite will generate an error if you try to name a table astable
. - The
INSERT INTO
command will fail if your data does not conform to the new table constraints. For instance, if the originaltest_table
contains twoid
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. - 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.
- android sqlite新添唯一键constraint错误的解决方法
- android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed错误原因
- 数据库中插入数据错误 android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
- android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed错误原因
- android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed错误原因
- android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed错误原因
- Could not find com.android.support.constraint:constraint-layout:1.0.2错误的解决
- sqlite constraint
- android数据库操作出现的 android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
- 导入项目出现:Could not find com.android.support.constraint:constraint-layout:2.0.0-alpha8的解决方法
- Gradle 同步时报错,Could not find com.android.support.constraint:constraint-layout:1.0.0-alpha9的解决方法
- 导入项目出现:Could not find com.android.support.constraint:constraint-layout:2.0.0-alpha8的解决方法
- Android:android.database.sqlite.SQLiteConstraintException:UNIQUE constraint failed
- 使用constraint时, animateWithDuration不起作用的解决方法
- android.database.sqlite.SQLiteConstraintException: error code 19: constraint failed
- android.database.sqlite.SQLiteConstraintException: UNIQUE constraint failed异常解决
- android.database.sqlite.SQLiteConstraintException: NOT NULL constraint failed: GROUP_FILE.FILE_NAME
- powerdesigner12报constraint name maximum length is limited错误解决方法
- c++参数调用方式的逆向
- 写工具的一些链接
- C++友元
- 物理standby转换为逻辑standby的步骤
- 继承 多态 虚函数
- android sqlite新添唯一键constraint错误的解决方法
- 启动centos 设置vsftp连接
- jsp中实现分页
- 加班与效率
- Scientific Conference
- WinForm中的Color( System.Drawing.Color)与Wpf中Color(System.Windows.Media.Color)互转
- INSTALL_FAILED_INSUFFICIENT_STORAGE 的解决方法
- Eclipse上GIT插件EGIT使用手册-——冲突解决方案
- 一步一步学数据结构之1--1(栈--两个队列实现)