diff --git a/docs/python-api.rst b/docs/python-api.rst
index 378b5d9..0bbf19d 100644
--- a/docs/python-api.rst
+++ b/docs/python-api.rst
@@ -354,6 +354,32 @@ Note that the ``pk`` and ``column_order`` parameters here are optional if you ar
 
 An ``upsert_all()`` method is also available, which behaves like ``insert_all()`` but performs upserts instead.
 
+.. _python_api_lookup_tables:
+
+Creating lookup tables
+======================
+
+A useful pattern when populating large tables in to break common values out into lookup tables. Consider a table of ``Trees``, where each tree has a species. Ideally these species would be split out into a separate ``Species`` table, with each one assigned an integer primary key that can be referenced from the ``Trees`` table ``species_id`` column.
+
+Calling ``db["Species"].lookup({"name": "Palm"})`` creates a table called ``Species`` (if one does not already exist) with two columns: ``id`` and ``name``. It sets up a unique constraint on the ``name`` column to guarantee it will not contain duplicate rows. It then inserts a new row with the ``name`` set to ``Palm`` and returns the new integer primary key value.
+
+If the ``Species`` table already exists, it will insert the new row and return the primary key. If a row with that ``name`` already exists, it will return the corresponding primary key value directly.
+
+If you call ``.lookup()`` against an existing table without the unique constraint it will attempt to add the constraint, raising an ``IntegrityError`` if the constraint cannot be created.
+
+If you pass in a dictionary with multiple values, both values will be used to insert or retrieve the corresponding ID and any unique constraint that is created will cover all of those columns, for example:
+
+.. code-block:: python
+
+    db["Trees"].insert({
+        "latitude": 49.1265976,
+        "longitude": 2.5496218,
+        "species": db["Species"].lookup({
+            "common_name": "Common Juniper",
+            "latin_name": "Juniperus communis"
+        })
+    })
+
 .. _python_api_add_column:
 
 Adding columns
diff --git a/sqlite_utils/db.py b/sqlite_utils/db.py
index c48483d..a86132b 100644
--- a/sqlite_utils/db.py
+++ b/sqlite_utils/db.py
@@ -224,6 +224,8 @@ class Database:
         single_pk = None
         if isinstance(pk, str):
             single_pk = pk
+            if pk not in [c[0] for c in column_items]:
+                column_items.insert(0, (pk, int))
         for column_name, column_type in column_items:
             column_extras = []
             if column_name == single_pk:
@@ -954,6 +956,29 @@ class Table:
             if col_name not in current_columns:
                 self.add_column(col_name, col_type)
 
+    def lookup(self, column_values):
+        # lookups is a dictionary - all columns will be used for a unique index
+        if self.exists:
+            self.add_missing_columns([column_values])
+            # TODO: Ensure we have a unique index on these
+            unique_column_sets = [set(i.columns) for i in self.indexes]
+            if set(column_values.keys()) not in unique_column_sets:
+                self.create_index(column_values.keys(), unique=True)
+            wheres = ["[{}] = ?".format(column) for column in column_values]
+            rows = list(
+                self.rows_where(
+                    " and ".join(wheres), [value for _, value in column_values.items()]
+                )
+            )
+            try:
+                return rows[0]["id"]
+            except IndexError:
+                return self.insert(column_values, pk="id").last_pk
+        else:
+            pk = self.insert(column_values, pk="id").last_pk
+            self.create_index(column_values.keys(), unique=True)
+            return pk
+
 
 def chunks(sequence, size):
     iterator = iter(sequence)

Utilities for building lookup tables
While building https://github.com/dogsheep/healthkit-to-sqlite I found a need for a neat mechanism for easily building lookup tables - tables where each unique value in a column is replaced by a foreign key to a separate table.

csvs-to-sqlite currently creates those with its "extract" mechanism - but that's written as custom code against Pandas. I'd like to eventually replace Pandas with sqlite-utils there.

See also #42 
diff --git a/tests/test_create.py b/tests/test_create.py
index 288a8ee..222a967 100644
--- a/tests/test_create.py
+++ b/tests/test_create.py
@@ -734,3 +734,8 @@ def test_cannot_provide_both_filename_and_memory():
         AssertionError, match="Either specify a filename_or_conn or pass memory=True"
     ):
         Database("/tmp/foo.db", memory=True)
+
+
+def test_creates_id_column(fresh_db):
+    last_pk = fresh_db.table("cats", pk="id").insert({"name": "barry"}).last_pk
+    assert [{"name": "barry", "id": last_pk}] == list(fresh_db["cats"].rows)
diff --git a/tests/test_lookup.py b/tests/test_lookup.py
new file mode 100644
index 0000000..ac8e1c2
--- /dev/null
+++ b/tests/test_lookup.py
@@ -0,0 +1,68 @@
+from sqlite_utils.db import Index
+import pytest
+
+
+def test_lookup_new_table(fresh_db):
+    species = fresh_db["species"]
+    palm_id = species.lookup({"name": "Palm"})
+    oak_id = species.lookup({"name": "Oak"})
+    cherry_id = species.lookup({"name": "Cherry"})
+    assert palm_id == species.lookup({"name": "Palm"})
+    assert oak_id == species.lookup({"name": "Oak"})
+    assert cherry_id == species.lookup({"name": "Cherry"})
+    assert palm_id != oak_id != cherry_id
+    # Ensure the correct indexes were created
+    assert [
+        Index(
+            seq=0,
+            name="idx_species_name",
+            unique=1,
+            origin="c",
+            partial=0,
+            columns=["name"],
+        )
+    ] == species.indexes
+
+
+def test_lookup_new_table_compound_key(fresh_db):
+    species = fresh_db["species"]
+    palm_id = species.lookup({"name": "Palm", "type": "Tree"})
+    oak_id = species.lookup({"name": "Oak", "type": "Tree"})
+    assert palm_id == species.lookup({"name": "Palm", "type": "Tree"})
+    assert oak_id == species.lookup({"name": "Oak", "type": "Tree"})
+    assert [
+        Index(
+            seq=0,
+            name="idx_species_name_type",
+            unique=1,
+            origin="c",
+            partial=0,
+            columns=["name", "type"],
+        )
+    ] == species.indexes
+
+
+def test_lookup_adds_unique_constraint_to_existing_table(fresh_db):
+    species = fresh_db.table("species", pk="id")
+    palm_id = species.insert({"name": "Palm"}).last_pk
+    species.insert({"name": "Oak"})
+    assert [] == species.indexes
+    assert palm_id == species.lookup({"name": "Palm"})
+    assert [
+        Index(
+            seq=0,
+            name="idx_species_name",
+            unique=1,
+            origin="c",
+            partial=0,
+            columns=["name"],
+        )
+    ] == species.indexes
+
+
+def test_lookup_fails_if_constraint_cannot_be_added(fresh_db):
+    species = fresh_db.table("species", pk="id")
+    species.insert_all([{"id": 1, "name": "Palm"}, {"id": 2, "name": "Palm"}])
+    # This will fail because the name column is not unique
+    with pytest.raises(Exception, match="UNIQUE constraint failed"):
+        species.lookup({"name": "Palm"})

pip install -e .[test]
parse_log_pytest
requirements.txt
apt-get update apt-get install -y sqlite3
3.7
requirements/base.txt
pytest --no-header -rA --tb=line --color=no -p no:cacheprovider -W ignore::DeprecationWarning
black==23.3.0
certifi @ file:///croot/certifi_1671487769961/work/certifi
click==8.1.8
click-default-group==1.2.4
exceptiongroup==1.2.2
importlib-metadata==6.7.0
iniconfig==2.0.0
mypy-extensions==1.0.0
packaging==24.0
pathspec==0.11.2
platformdirs==4.0.0
pluggy==1.2.0
pytest==7.4.4
-e git+https://github.com/simonw/sqlite-utils.git@f3a4c3d3ee6475a6caf3c9606656dbaf1df020b7#egg=sqlite_utils
tabulate==0.9.0
tomli==2.0.1
typed-ast==1.5.5
typing_extensions==4.7.1
zipp==3.15.0

name: sqlite-utils
channels:
  - defaults
  - https://repo.anaconda.com/pkgs/main
  - https://repo.anaconda.com/pkgs/r
  - conda-forge
dependencies:
  - _libgcc_mutex=0.1=main
  - _openmp_mutex=5.1=1_gnu
  - ca-certificates=2025.2.25=h06a4308_0
  - certifi=2022.12.7=py37h06a4308_0
  - ld_impl_linux-64=2.40=h12ee557_0
  - libffi=3.4.4=h6a678d5_1
  - libgcc-ng=11.2.0=h1234567_1
  - libgomp=11.2.0=h1234567_1
  - libstdcxx-ng=11.2.0=h1234567_1
  - ncurses=6.4=h6a678d5_0
  - openssl=1.1.1w=h7f8727e_0
  - pip=22.3.1=py37h06a4308_0
  - python=3.7.16=h7a1cb2a_0
  - readline=8.2=h5eee18b_0
  - setuptools=65.6.3=py37h06a4308_0
  - sqlite=3.45.3=h5eee18b_0
  - tk=8.6.14=h39e8969_0
  - wheel=0.38.4=py37h06a4308_0
  - xz=5.6.4=h5eee18b_1
  - zlib=1.2.13=h5eee18b_1
  - pip:
      - black==23.3.0
      - click==8.1.8
      - click-default-group==1.2.4
      - exceptiongroup==1.2.2
      - importlib-metadata==6.7.0
      - iniconfig==2.0.0
      - mypy-extensions==1.0.0
      - packaging==24.0
      - pathspec==0.11.2
      - platformdirs==4.0.0
      - pluggy==1.2.0
      - pytest==7.4.4
      - tabulate==0.9.0
      - tomli==2.0.1
      - typed-ast==1.5.5
      - typing-extensions==4.7.1
      - zipp==3.15.0
prefix: /opt/conda/envs/sqlite-utils

tests/test_create.py::test_creates_id_column tests/test_lookup.py::test_lookup_new_table tests/test_lookup.py::test_lookup_new_table_compound_key tests/test_lookup.py::test_lookup_adds_unique_constraint_to_existing_table tests/test_lookup.py::test_lookup_fails_if_constraint_cannot_be_added
tests/test_create.py::test_create_table tests/test_create.py::test_create_table_compound_primary_key tests/test_create.py::test_create_table_with_bad_defaults tests/test_create.py::test_create_table_with_defaults tests/test_create.py::test_create_table_with_bad_not_null tests/test_create.py::test_create_table_with_not_null tests/test_create.py::test_create_table_from_example[example0-expected_columns0] tests/test_create.py::test_create_table_from_example[example1-expected_columns1] tests/test_create.py::test_create_table_from_example[example2-expected_columns2] tests/test_create.py::test_create_table_from_example_with_compound_primary_keys tests/test_create.py::test_create_table_column_order[True] tests/test_create.py::test_create_table_column_order[False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[True-foreign_key_specification0-False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[True-foreign_key_specification1-False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[True-foreign_key_specification2-False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[True-foreign_key_specification3-False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[True-foreign_key_specification4-NoObviousTable] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[True-foreign_key_specification5-AssertionError] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[True-foreign_key_specification6-AlterError] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[True-foreign_key_specification7-AssertionError] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[False-foreign_key_specification0-False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[False-foreign_key_specification1-False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[False-foreign_key_specification2-False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[False-foreign_key_specification3-False] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[False-foreign_key_specification4-NoObviousTable] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[False-foreign_key_specification5-AssertionError] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[False-foreign_key_specification6-AlterError] tests/test_create.py::test_create_table_works_for_m2m_with_only_foreign_keys[False-foreign_key_specification7-AssertionError] tests/test_create.py::test_create_error_if_invalid_foreign_keys tests/test_create.py::test_add_column[nickname-str-None-CREATE tests/test_create.py::test_add_column[dob-date-None-CREATE tests/test_create.py::test_add_column[age-int-None-CREATE tests/test_create.py::test_add_column[weight-float-None-CREATE tests/test_create.py::test_add_column[text-TEXT-None-CREATE tests/test_create.py::test_add_column[integer-INTEGER-None-CREATE tests/test_create.py::test_add_column[float-FLOAT-None-CREATE tests/test_create.py::test_add_column[blob-blob-None-CREATE tests/test_create.py::test_add_column[default_str-None-None-CREATE tests/test_create.py::test_add_column[nickname-str--CREATE tests/test_create.py::test_add_column[nickname-str-dawg's tests/test_create.py::test_add_foreign_key tests/test_create.py::test_add_foreign_key_error_if_column_does_not_exist tests/test_create.py::test_add_foreign_key_error_if_other_table_does_not_exist tests/test_create.py::test_add_foreign_key_error_if_already_exists tests/test_create.py::test_add_foreign_keys tests/test_create.py::test_add_column_foreign_key tests/test_create.py::test_add_foreign_key_guess_table tests/test_create.py::test_index_foreign_keys tests/test_create.py::test_insert_row_alter_table[True-extra_data0-expected_new_columns0] tests/test_create.py::test_insert_row_alter_table[True-extra_data1-expected_new_columns1] tests/test_create.py::test_insert_row_alter_table[True-extra_data2-expected_new_columns2] tests/test_create.py::test_insert_row_alter_table[False-extra_data0-expected_new_columns0] tests/test_create.py::test_insert_row_alter_table[False-extra_data1-expected_new_columns1] tests/test_create.py::test_insert_row_alter_table[False-extra_data2-expected_new_columns2] tests/test_create.py::test_upsert_rows_alter_table[True] tests/test_create.py::test_upsert_rows_alter_table[False] tests/test_create.py::test_create_index[columns0-None-expected_index0] tests/test_create.py::test_create_index[columns1-None-expected_index1] tests/test_create.py::test_create_index[columns2-age_index-expected_index2] tests/test_create.py::test_create_index_unique tests/test_create.py::test_create_index_if_not_exists tests/test_create.py::test_insert_dictionaries_and_lists_as_json[data_structure0] tests/test_create.py::test_insert_dictionaries_and_lists_as_json[data_structure1] tests/test_create.py::test_insert_dictionaries_and_lists_as_json[data_structure2] tests/test_create.py::test_insert_dictionaries_and_lists_as_json[data_structure3] tests/test_create.py::test_insert_dictionaries_and_lists_as_json[data_structure4] tests/test_create.py::test_insert_thousands_using_generator tests/test_create.py::test_insert_thousands_ignores_extra_columns_after_first_100 tests/test_create.py::test_insert_ignore tests/test_create.py::test_insert_hash_id tests/test_create.py::test_create_view tests/test_create.py::test_vacuum tests/test_create.py::test_works_with_pathlib_path tests/test_create.py::test_cannot_provide_both_filename_and_memory
sqlite_utils/db.py docs/python-api.rst
sqlite_utils/db.py docs/python-api.rst