Glorp: How to create Foreign Keys with ON DELETE SET NULL

In Glorp, you can create foreign keys between tables. By default, such a foreign key will result in problems when you delete an entry in the table with the key attribute.

So if you need to create a foreign key that does not restrict deletion of a parent row, you want to add a foreign key that defines a constraint of ON DELETE SET NULL. Or maybe you want to forward deletion with ON DELETE CASCADE.

It took me a few minutes to figure out how to define such a key, so I write it down here for all to see and for me to remember.

It turns out all you have to do is to define a suffixExpression for the foreign key in your tableFor… method like this:

fkeyId := aTable createFieldNamed: 'other_id' type: platform int4.
aTable
	addForeignKeyFrom: fkeyId
	to: ((self tableNamed: 'THEOTHER') fieldNamed: 'id')
	suffixExpression: 'ON DELETE SET NULL'

Advertisements