add_foreign_key(...., ignore=True)

[simonw]

When using this library I often find myself wanting to "add this foreign key, but only if it doesn't exist yet". The ignore=True parameter is increasingly being used for this else where in the library (e.g. in create_view()).

[simonw]

I would add ignore=True/False as a parameter here:

sqlite-utils/sqlite_utils/db.py

Lines 731 to 761 in af3f81b

	def add_foreign_key(self, column, other_table=None, other_column=None):
	# Ensure column exists
	if column not in self.columns_dict:
	raise AlterError("No such column: {}".format(column))
	# If other_table is not specified, attempt to guess it from the column
	if other_table is None:
	other_table = self.guess_foreign_table(column)
	# If other_column is not specified, detect the primary key on other_table
	if other_column is None:
	other_column = self.guess_foreign_column(other_table)
	# Sanity check that the other column exists
	if (
	not [c for c in self.db[other_table].columns if c.name == other_column]
	and other_column != "rowid"
	):
	raise AlterError("No such column: {}.{}".format(other_table, other_column))
	# Check we do not already have an existing foreign key
	if any(
	fk
	for fk in self.foreign_keys
	if fk.column == column
	and fk.other_table == other_table
	and fk.other_column == other_column
	):
	raise AlterError(
	"Foreign key already exists for {} => {}.{}".format(
	column, other_table, other_column
	)
	)
	self.db.add_foreign_keys([(self.name, column, other_table, other_column)])

[simonw]

Here's how it works for create_view:

sqlite-utils/sqlite_utils/db.py

Lines 327 to 336 in af3f81b

	def create_view(self, name, sql, ignore=False, replace=False):
	assert not (
	ignore and replace
	), "Use one or the other of ignore/replace, not both"
	create_sql = "CREATE VIEW {name} AS {sql}".format(name=name, sql=sql)
	if ignore or replace:
	# Does view exist already?
	if name in self.view_names():
	if ignore:
	return self

[simonw]

Question: if you use ignore=True should it still raise an error if one of the columns you referenced does not exist?

That's tricky. I'm leaning towards "that's still an error".

Are we ignoring an already existing foreign key, or ignoring all errors?

[simonw]

Here's an example of some code that would be cleaner with this mechanism: https://github.com/dogsheep/swarm-to-sqlite/blob/f4a82633da927cde672c9d9af92930bfca2e3ddf/swarm_to_sqlite/utils.py#L120-L143