2.34.0-RC1 upgrade fails if Jethro has services for since-deleted congregations

[jefft]

Attempting to upgrade my 2.33.0 Jethro to 2.34.0-RC1, I get an error running upgrades/2023-upgrade-to-2.34.sql:

ERROR 1452 (23000) at line 44: Cannot add or update a child row: a foreign key constraint fails (`coastec_jethro`.`#sql
-alter-11ad5c-2baf5`, CONSTRAINT `service_congregationid` FOREIGN KEY (`congregationid`) REFERENCES `congregation` (`id
`))

The relevant line of SQL is tightening up the schema, so congregations cannot be deleted if they have associated services (#834):

jethro-pmm/upgrades/2023-upgrade-to-2.34.sql

Lines 44 to 45 in c19e9ff

	ALTER TABLE service
	ADD CONSTRAINT `service_congregationid` FOREIGN KEY (congregationid) REFERENCES congregation(id) ON DELETE RESTRICT;

Our Jethro did once have 3 temporary congregations, and a number of associated services.

I'm all for tightening the schema, but then the upgrade SQL needs to deal with the current messy reality somehow.

[jefft]

The attached PR (pls ignore the first) resolves this by recreating the deleted congregations, so the services are no longer dangling and the foreign key constraint can be applied.

[tbar0970]

Nice. Thanks.

…

On Sun, 20 Aug 2023 at 1:54 am, Jeff Turner ***@***.***> wrote: The attached PR <#941> (pls ignore the first) resolves this by recreating the deleted congregations, so the services are no longer dangling and the foreign key constraint can be applied. — Reply to this email directly, view it on GitHub <#940 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAQI5LFHUWIRZZFZSFPGWETXWDOQHANCNFSM6AAAAAA3WUXR6I> . You are receiving this because you are subscribed to this thread.Message ID: ***@***.***>