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:

sqlite-utils/sqlite_utils/db.py

Lines 1523 to 1540 in 622c3a5

	@property
	def foreign_keys(self) -> List["ForeignKey"]:
	"List of foreign keys defined on this table."
	fks = []
	for row in self.db.execute(
	"PRAGMA foreign_key_list([{}])".format(self.name)
	).fetchall():
	if row is not None:
	id, seq, table_name, from_, to_, on_update, on_delete, match = row
	fks.append(
	ForeignKey(
	table=self.name,
	column=from_,
	other_table=table_name,
	other_column=to_,
	)
	)
	return fks

[simonw]

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

sqlite-utils/sqlite_utils/db.py

Lines 148 to 150 in 622c3a5

	ForeignKey = namedtuple(
	"ForeignKey", ("table", "column", "other_table", "other_column")
	)

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:
   # ...