SQLITE: how to remove an unamed primary key

Question

I have a sqlite table that was originally created with:

PRIMARY KEY (`column`);

I now need to remove that primary key and create a new one. Creating a new one is easy, but removing the original seems to be the hard part. If I do

.indices  tablename

I don't get the primary key. Some programs show the primary key as

Indexes: 1
    [] PRIMARY

The index name is typically in the [].

Any ideas?

Answer 1

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'll 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 seeing if that name is anything useful.

Answer 2

I the database glossary; a primary-key is a type of index where the index order is typically results in the physical ordering of the raw database records. That said any database engine that allows the primary key to be changed is likely reordering the database... so most do not and the operation is up to the programmer to create a script to rename the table and create a new one. So if you want to change the PK there is no magic SQL.

Answer 3

You can't.

PRAGMA INDEX_LIST('MyTable');

will give you a list of indices. This will include the automatically generated index for the primary key which will be called something like '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 re-create the table without the primary key.