Support for compound (composite) foreign keys

[simonw]

It turns out SQLite supports composite foreign keys: https://www.sqlite.org/foreignkeys.html#fk_composite

Their example looks like this:

CREATE TABLE album(
  albumartist TEXT,
  albumname TEXT,
  albumcover BINARY,
  PRIMARY KEY(albumartist, albumname)
);

CREATE TABLE song(
  songid     INTEGER,
  songartist TEXT,
  songalbum TEXT,
  songname   TEXT,
  FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname)
);

Here's what that looks like in sqlite-utils:

In [1]: import sqlite_utils                                                                                                                

In [2]: import sqlite3                                                                                                                     

In [3]: conn = sqlite3.connect(":memory:")                                                                                                 

In [4]: conn                                                                                                                               
Out[4]: <sqlite3.Connection at 0x1087186c0>

In [5]: conn.executescript(""" 
   ...: CREATE TABLE album( 
   ...:   albumartist TEXT, 
   ...:   albumname TEXT, 
   ...:   albumcover BINARY, 
   ...:   PRIMARY KEY(albumartist, albumname) 
   ...: ); 
   ...:  
   ...: CREATE TABLE song( 
   ...:   songid     INTEGER, 
   ...:   songartist TEXT, 
   ...:   songalbum TEXT, 
   ...:   songname   TEXT, 
   ...:   FOREIGN KEY(songartist, songalbum) REFERENCES album(albumartist, albumname) 
   ...: ); 
   ...: """)                                                                                                                               
Out[5]: <sqlite3.Cursor at 0x1088def10>

In [6]: db = sqlite_utils.Database(conn)                                                                                                   

In [7]: db.tables                                                                                                                          
Out[7]: 
[<Table album (albumartist, albumname, albumcover)>,
 <Table song (songid, songartist, songalbum, songname)>]

In [8]: db.tables[0].foreign_keys                                                                                                          
Out[8]: []

In [9]: db.tables[1].foreign_keys                                                                                                          
Out[9]: 
[ForeignKey(table='song', column='songartist', other_table='album', other_column='albumartist'),
 ForeignKey(table='song', column='songalbum', other_table='album', other_column='albumname')]

The table appears to have two separate foreign keys, when actually it has a single compound composite foreign key.

[simonw]

Here's what's missing:

In [11]: db.conn.execute('PRAGMA foreign_key_list(song)').fetchall()                                                                       
Out[11]: 
[(0,
  0,
  'album',
  'songartist',
  'albumartist',
  'NO ACTION',
  'NO ACTION',
  'NONE'),
 (0, 1, 'album', 'songalbum', 'albumname', 'NO ACTION', 'NO ACTION', 'NONE')]

Compare with this code here:

sqlite-utils/sqlite_utils/db.py

Lines 563 to 579 in d0cdaaa

	@property
	def foreign_keys(self):
	fks = []
	for row in self.db.conn.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

The first two columns returned by PRAGMA foreign_key_list(table) are id and seq - these show when two foreign key records are part of the same compound foreign key. sqlite-utils entirely ignores those at the moment.

[simonw]

Relevant discussion: simonw/sqlite-generate#8 (comment)

[simonw]

So there are two sides to supporting this:

• Being able to sensibly introspect composite foreign keys
• Being able to define composite foreign keys when creating a table