Document how to add a primary key to a rowid table using `sqlite-utils transform --pk` #403

fgregg · 2022-02-08T01:39:40Z

Original title: Add option for adding a new, serial, primary key

sometimes we have tables that don't have primary keys, but ought to have them. we can use rowid for that, but it would often be nicer to have an explicit primary key. using the current value of rowid would be fine.

fgregg · 2022-02-08T01:45:15Z

you can hack something like this to achieve this result:

sqlite-utils convert my_database my_table rowid "{'id': value}" --multi

simonw · 2022-02-08T19:23:05Z

This is already possible using sqlite-utils transform like so:

% echo '[{"name": "Barry"}, {"name": "Sandra"}]' | sqlite-utils insert rowid.db records -
% sqlite-utils schema rowid.db                                                           
CREATE TABLE [records] (
   [name] TEXT
);
% sqlite-utils rows rowid.db records
[{"name": "Barry"},
 {"name": "Sandra"}]
% sqlite-utils transform rowid.db records --pk id
% sqlite-utils rows rowid.db records
[{"id": 1, "name": "Barry"},
 {"id": 2, "name": "Sandra"}]
% sqlite-utils schema rowid.db
CREATE TABLE "records" (
   [id] INTEGER PRIMARY KEY,
   [name] TEXT
);
% echo '[{"name": "Barry 2"}, {"name": "Sandra 2"}]' | sqlite-utils insert rowid.db records -
% sqlite-utils rows rowid.db records                                                         
[{"id": 1, "name": "Barry"},
 {"id": 2, "name": "Sandra"},
 {"id": 3, "name": "Barry 2"},
 {"id": 4, "name": "Sandra 2"}]

It's not covered in the documentation though: https://sqlite-utils.datasette.io/en/3.23/cli.html#transforming-tables

simonw · 2022-02-08T19:36:06Z

New documentation: https://sqlite-utils.datasette.io/en/latest/cli.html#adding-a-primary-key-to-a-rowid-table

fgregg · 2022-02-09T04:22:43Z

dddoooope

Refs ##397, #398, #401, #403, #404, #405, #407

simonw added the documentation label Feb 8, 2022

simonw changed the title ~~add option for adding a new, serial, primary key~~ Document how to add a primary key to a rowid table using sqlite-utils transform --pk Feb 8, 2022

simonw closed this as completed in 3acc2f1 Feb 8, 2022

simonw added a commit that referenced this issue Feb 16, 2022

Release 3.24

757f103

Refs ##397, #398, #401, #403, #404, #405, #407

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Document how to add a primary key to a rowid table using `sqlite-utils transform --pk` #403

Document how to add a primary key to a rowid table using `sqlite-utils transform --pk` #403

fgregg commented Feb 8, 2022 •

edited by simonw

Loading

fgregg commented Feb 8, 2022 •

edited

Loading

simonw commented Feb 8, 2022 •

edited

Loading

simonw commented Feb 8, 2022

fgregg commented Feb 9, 2022

Document how to add a primary key to a rowid table using sqlite-utils transform --pk #403

Document how to add a primary key to a rowid table using sqlite-utils transform --pk #403

Comments

fgregg commented Feb 8, 2022 • edited by simonw Loading

fgregg commented Feb 8, 2022 • edited Loading

simonw commented Feb 8, 2022 • edited Loading

simonw commented Feb 8, 2022

fgregg commented Feb 9, 2022

Document how to add a primary key to a rowid table using `sqlite-utils transform --pk` #403

Document how to add a primary key to a rowid table using `sqlite-utils transform --pk` #403

fgregg commented Feb 8, 2022 •

edited by simonw

Loading

fgregg commented Feb 8, 2022 •

edited

Loading

simonw commented Feb 8, 2022 •

edited

Loading