Cannot drop or change primary key

[Former user]

Originally reported on Google Code with ID 79

I can not change primary key of an empty table.

Reported by digitaalinenhemuli on 2012-01-11 19:40:53

[Christopher Kramer]

``` Thanks for reporting this. Changing primary key is just not supported at the moment, as SQLite does not support the ALTER TABLE statement to do so.

You can add a UNIQUE index (do not allow duplicates), but you cannot remove the existing primary key (at the moment).

I can think of 2 possible solutions to implement this: 1. Remove primary key and add a unique key on the new column (I am not sure SQLite allows us to remove the primary key, though). 2. Recreate the table, as we do when renaming columns/types etc. Relates to issue #12 . ```

Reported by `crazy4chrissi` on 2012-06-02 20:38:10 - Labels added: Type-Enhancement - Labels removed: Type-Defect

[Christopher Kramer]

I think I'll implement this for 1.9.4 in my alter table function.
Not that difficult I'd say.

Reported by crazy4chrissi on 2012-10-30 22:37:38

[Christopher Kramer]

Reported by crazy4chrissi on 2012-10-30 22:37:48 - Status changed: Accepted

[Christopher Kramer]

Reported by crazy4chrissi on 2013-12-23 23:00:24 - Labels added: Target-1.9.5

[Christopher Kramer]

With r444, it is now possible to add primary keys (only if the table has no primary
key yet). But it is not yet possible to change or drop them.

Reported by crazy4chrissi on 2013-12-29 23:01:54

[Christopher Kramer]

Reported by crazy4chrissi on 2013-12-29 23:19:12

[Christopher Kramer]

Dropping primary keys is missing. Once this is done, changing is only dropping the old
one and creating the new one (which is now possible), so this is easy then.

Reported by crazy4chrissi on 2013-12-29 23:20:55

[Christopher Kramer]

Reported by crazy4chrissi on 2014-01-02 12:40:21 - Labels added: Target-1.9.6 - Labels removed: Target-1.9.5

[Christopher Kramer]

Dropping primary keys is now party working in the current development version.
It is now possible to drop a primary key from the SQL tab by issuing:
ALTER TABLE "mytable" DROP PRIMARY KEY;
Note that like with every ALTER TABLE command that phphLiteAdmin provides as an SQLite
workaround, slightly different syntax (no quotes/single quotes/...) might not work.

Currently, only primary keys created as column constraints can be dropped. Example:
CREATE TABLE "t" ("a" PRIMARY KEY, "b");
ALTER TABLE "t" DROP PRIMARY KEY;
What does not work yet is dropping primary keys defined as a table constraint. Example:
CREATE TABLE "t" ("a", "b", PRIMARY KEY ("a") );
ALTER TABLE "t" DROP PRIMARY KEY;
Will not work. This is still an open todo.

Another problem are named constraints with names in quotes before the primary key constraint.
CREATE TABLE "t" ("a" CONSTRAINT "bla" NOT NULL PRIMARY KEY );
ALTER TABLE "t" DROP PRIMARY KEY;
Will not work, but this will:
CREATE TABLE "t" ("a" CONSTRAINT bla NOT NULL PRIMARY KEY );
ALTER TABLE "t" DROP PRIMARY KEY;
I still need to find the bug here.

Reported by crazy4chrissi on 2015-01-03 23:09:19 - Status changed: Started