Foreign key labels for tables with unique indexes on multiple columns

[simonw]

While hacking around with https://github.com/datasette/datasette-acl I noticed this table:

Those resource_id integers link to this table:

CREATE TABLE acl_resources (
    id integer primary key autoincrement,
    database text not null,
    resource text,
    unique(database, resource)
);

In this particular case, showing the database and then the resource as the foreign key label would make sense - both of those are strings and they are unique together, which makes them a valid label.

I poked around with Claude and came up with this query:

select
  m.name as table_name,
  json_group_array(ii.name) as unique_column_names
from
  sqlite_master as m,
  pragma_index_list(m.name) AS il,
  pragma_index_info(il.name) AS ii
where
  m.type = 'table'
  and il.origin = 'u'
group by
  il.name;

Which returns this for that schema:

This could be a useful alternative way to determine default foreign key labels.

[simonw]

Currently returns no results at all against the fixtures database: https://latest.datasette.io/fixtures/-/query?sql=select%0D%0A++m.name+as+table_name%2C%0D%0A++json_group_array%28ii.name%29+AS+unique_column_names%0D%0AFROM%0D%0A++sqlite_master+AS+m%2C%0D%0A++pragma_index_list%28m.name%29+AS+il%2C%0D%0A++pragma_index_info%28il.name%29+AS+ii%0D%0AWHERE%0D%0A++m.type+%3D+%27table%27%0D%0A++AND+il.origin+%3D+%27u%27%0D%0AGROUP+BY%0D%0A++il.name%3B

The compound unique column pattern used in Datasette internal isn't as common as I thought!