Represent compound foreign keys in table.foreign_keys output

[simonw]

Given this schema:

CREATE TABLE departments (
    campus_name TEXT NOT NULL,
    dept_code TEXT NOT NULL,
    dept_name TEXT,
    PRIMARY KEY (campus_name, dept_code)
);
CREATE TABLE courses (
    course_code TEXT PRIMARY KEY,
    course_name TEXT,
    campus_name TEXT NOT NULL,
    dept_code TEXT NOT NULL,
    FOREIGN KEY (campus_name, dept_code) REFERENCES departments(campus_name, dept_code)
);

The output of db["courses"].foreign_keys right now is:

[ForeignKey(table='courses', column='campus_name', other_table='departments', other_column='campus_name'),
 ForeignKey(table='courses', column='dept_code', other_table='departments', other_column='dept_code')]

Which suggests two normal foreign keys, not one compound foreign key.

[simonw]

Digging in a bit more:

>>> pprint(list(db.query('PRAGMA foreign_key_list(courses)')))
[{'from': 'campus_name',
  'id': 0,
  'match': 'NONE',
  'on_delete': 'NO ACTION',
  'on_update': 'NO ACTION',
  'seq': 0,
  'table': 'departments',
  'to': 'campus_name'},
 {'from': 'dept_code',
  'id': 0,
  'match': 'NONE',
  'on_delete': 'NO ACTION',
  'on_update': 'NO ACTION',
  'seq': 1,
  'table': 'departments',
  'to': 'dept_code'}]

I think the way you tell it's a compound foreign key is that both of those have the same id value - of 0 - but they then have two different seq values of 0 and 1.

Right now I ignore those columns entirely: https://github.com/simonw/sqlite-utils/blob/622c3a5a7dd53a09c029e2af40c2643fe7579340/sqlite_utils/db.py#L1523-L1540

[simonw]

Changing this without breaking backwards compatibility (and forcing a 4.0 release) will be tricky, because ForeignKey() is a namedtuple:

https://github.com/simonw/sqlite-utils/blob/622c3a5a7dd53a09c029e2af40c2643fe7579340/sqlite_utils/db.py#L148-L150

I could swap it out for a dataclass and add those extra columns, but I need to make sure that code like this still works:

for table, column, other_table, other_column in table.foreign_keys:
   # ...