Open simonw opened 2 weeks ago
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!
While hacking around with https://github.com/datasette/datasette-acl I noticed this table:
Those
resource_id
integers link to this table: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:
Which returns this for that schema:
This could be a useful alternative way to determine default foreign key labels.