SQLITE: unable to drop unnamed primary key
I have a sqlite table that was originally created with
PRIMARY KEY (`column`);
Now I need to drop this primary key and create a new one. Creating a new one is easy, but deleting the original seems difficult. If i do
.indices tablename
I am not getting the primary key. Some programs show the primary key as
Indexes: 1
[] PRIMARY
The index name is usually found in []
.
Any ideas?
a source to share
You can not.
PRAGMA INDEX_LIST('MyTable');
will give you a list of indices. This will include an auto-generated index for the primary key, which will be named as 'sqlite_autoindex_MyTable_1'
But unfortunately you cannot drop this index ...
sqlite> drop index sqlite_autoindex_MyTable_1; SQL error: index associated with UNIQUE or PRIMARY KEY constraint cannot be dropped
All you can do is recreate the table without the primary key.
a source to share
I am a database glossary; a primary key is a type of index, where the index order usually results in the physical ordering of the original database records. However, any database engine that allows the primary key to be changed is likely to reorder the database ... so most of them don't work, and it's up to the programmer to create a script to rename the table and create a new one. So if you want to change PK, there is no magic SQL.
a source to share
select * from sqlite_master;
table|x|x|2|CREATE TABLE x (a text, b text, primary key (`a`))
index|sqlite_autoindex_x_1|x|3|
You will see that the second row returned from my quick hack has the index name in the second column and the table name in the third. Try to see that this name is nothing useful.
a source to share