adding a primary key to an already existing table

(1) By jose isaias cabrera (jicman) on 2023-09-15 18:00:37 [link] [source]

Greetings!

Imagine...

sqlite> create table t (a, b, primary key (a, b));
VM-steps: 41
Run Time: real 0.010 user 0.000000 sys 0.015625
sqlite> .schema
CREATE TABLE t (a, b, primary key (a, b));
sqlite> alter table t add column c;
VM-steps: 51
Run Time: real 0.000 user 0.000000 sys 0.000000
sqlite> .schema
CREATE TABLE t (a, b, c, primary key (a, b));
sqlite>

How do I add that new c field to also be part of primary key list?

I've searched around and I couldn't find anything. I found something for MS SQL, but nothing for SQLIte. I actually tried it,

sqlite> alter table t add constraint c primary key(c);
Run Time: real 0.000 user 0.000000 sys 0.000000
Parse error: near "constraint": syntax error
  alter table t add constraint c primary key(c);
                    ^--- error here

But, as you can see, it didn't work. I know it's something simple, but I could not find it on the docs (I didn't look long). Any help would be greatly appreciated. Thanks.

(2) By David Raymond (dvdraymond) on 2023-09-15 19:03:20 in reply to 1 [source]

Changing the primary key on an existing table is not something you can currently do.

You're basically left with:
-Make a new table in the layout you're looking for.
-Copy the data to the new table.
-Drop the original table.
-Rename the new table if desired.

(3) By jose isaias cabrera (jicman) on 2023-09-15 19:30:34 in reply to 2 [link] [source]

Oh, ok.

(4) By punkish on 2023-09-16 10:07:23 in reply to 1 [link] [source]

But, as you can see, it didn't work. I know it's something simple, but I could not find it on the docs (I didn't look long). Any help would be greatly appreciated. Thanks.

From the docs, specifically https://sqlite.org/lang_altertable.html#alter_table_add_column

The column may not have a PRIMARY KEY or UNIQUE constraint.

(5) By jose isaias cabrera (jicman) on 2023-09-16 16:44:04 in reply to 4 [link] [source]

The column may not have a PRIMARY KEY or UNIQUE constraint.

Thanks. If I had only read it. :-(

(6) By Chris Locke (chrisjlocke1) on 2023-09-16 19:23:56 in reply to 5 [link] [source]

But you can simply create the new table as you like and copy the data into it.
Wouldn't this solve your problem?

(7) By jose isaias cabrera (jicman) on 2023-09-18 12:39:37 in reply to 6 [link] [source]

Wouldn't this solve your problem?

It does. What I meant was if I only had read the instructions totally, this post would not have happened. :-)