simonw / sqlite-utils

Python CLI utility and library for manipulating SQLite databases
https://sqlite-utils.datasette.io
Apache License 2.0
1.62k stars 109 forks source link

Ability to define unique columns when creating a table #558

Open aguinane opened 1 year ago

aguinane commented 1 year ago

When creating a new table, it would be good to have an option to set unique columns similar to how not_null is set.

from sqlite_utils import Database

columns = {"mRID": str, "name": str}
db = Database("example.db")
db["ExampleTable"].create(columns, pk="mRID", not_null=["mRID"], if_not_exists=True)
db["ExampleTable"].create_index(["mRID"], unique=True, if_not_exists=True)

So something like this would add the UNIQUE flag to the table definition.

db["ExampleTable"].create(columns, pk="mRID", not_null=["mRID"], unique=["mRID"], if_not_exists=True)
CREATE TABLE ExampleTable (
    mRID TEXT PRIMARY KEY
              NOT NULL
              UNIQUE,
    name TEXT
);