Delete a single record from an existing table

[simonw]

API design:

POST /db/table/row-pks/-/delete
Or...
DELETE /db/table/row-pks/-/delete

I'm just going to do POST for the moment, like I did here:

• API to drop a table #1874

Permission: delete-row

Still needed:

• Tests for rowid tables
• Tests for compound primary keys

[simonw]

Still needs tests that cover compound primary keys and rowid tables.

[simonw]

I'm a bit nervous about calling .delete() with the pk_values - can I be sure they are in the correct order?

datasette/datasette/views/row.py

Lines 188 to 190 in 00632de

	# Delete table
	def delete_row(conn):
	sqlite_utils.Database(conn)[table].delete(pk_values)

[simonw]

I need to think about what happens if you delete a row that is the target of a foreign key from another row.

https://www.sqlite.org/foreignkeys.html#fk_enable shows that SQLite will only actively enforce these relationships (e.g. throw an error if you try to delete a row that is referenced by another row) if you first run PRAGMA foreign_keys = ON; against the connection.

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.)

I don't actually believe that the SQLite maintainers will ever make that the default though.

Datasette doesn't turn these on at the moment, but it could be turned on by a prepare_connection() plugin.

[simonw]

It looks like SQLite has features for this already: https://www.sqlite.org/foreignkeys.html#fk_actions

Foreign key ON DELETE and ON UPDATE clauses are used to configure actions that take place when deleting rows from the parent table (ON DELETE), or modifying the parent key values of existing rows (ON UPDATE). A single foreign key constraint may have different actions configured for ON DELETE and ON UPDATE. Foreign key actions are similar to triggers in many ways.

On that basis, I'm not going to implement anything additional in the .../-/delete endpoint relating to foreign keys. Developers who want special treatment of them can do that with a combination of a plugin (maybe I'll build a datasette-enable-foreign-keys plugin) and tables created using those ON DELETE clauses.