How to change primary key of a record in sqlite?

Question

I have table that has a TEXT primary key

CREATE TABLE tbl1{
  a1 TEXT PRIMARY KEY,
  ...
);

(the a1 column is a foreign key inside another table)

How can I change values of a1?

If I do

UPDATE tbl1 SET a1 = ? WHERE a1 = ?

I get a constrain violation error

Answer 1

You should never change primary keys; it would be a better idea to use an INTEGER PRIMARY KEY and have the actual URL be a normal data column.

If you really want change a key that is the target of a foreign key, you should declare the foreign key constraint as deferred so that you are able to adjust the foreign key value in the same transaction.

Answer 2

The problem is that your table has single column that is the primary key and is a foreign key to another table. This suggests that the database design of the database is wrong. Unless you can change the database structure you need to add the correct values in that other table to change your primary key value. That is "insert into table constraintingTable(key,val) values (A,B)" and then execute update tbl set a1 = A where a1 = KEY. Ignore the people telling you that primary keys should never be changed, there is a body of theory on how primary keys should be built. A primary key should uniquely identify the value columns of a row (see database theory), for instance typical keys are PNR, SSN, Serial Number, Mobile Phone number, and sometimes multi values like Name, Address, Street, Country. Generated keys should only be used if you generate new values or you have practical problems using a proper primary key.