Duplicate BLSession -> BeamCalendar relationship?

There appears to be a duplicate relationship between BLSession -> BeamCalendar, is this correct?

Lines 750 to 753 in 0ccd996

    
           CONSTRAINT `BLSession_fk_beamCalendarId` FOREIGN KEY (`beamCalendarId`) REFERENCES `BeamCalendar` (`beamCalendarId`) ON DELETE SET NULL ON UPDATE CASCADE, 
        
           CONSTRAINT `BLSession_ibfk_1` FOREIGN KEY (`proposalId`) REFERENCES `Proposal` (`proposalId`) ON DELETE CASCADE ON UPDATE CASCADE, 
        
           CONSTRAINT `BLSession_ibfk_2` FOREIGN KEY (`beamLineSetupId`) REFERENCES `BeamLineSetup` (`beamLineSetupId`) ON DELETE CASCADE ON UPDATE CASCADE, 
        
           CONSTRAINT `BLSession_ibfk_3` FOREIGN KEY (`beamCalendarId`) REFERENCES `BeamCalendar` (`beamCalendarId`)

The resulting SQLAlchemy ORM class has this relationship duplicated:

https://github.com/DiamondLightSource/ispyb-api/blob/c59197cf8352056148cc9d3aae85c4f176880d0d/src/ispyb/sqlalchemy/_auto_db_schema.py#L2394-L2401

which leads to the following error when using the models:

SAWarning: relationship 'BLSession.BeamCalendar1' will copy column BeamCalendar.beamCalendarId to column BLSession.beamCalendarId, which conflicts with relationship(s): 'BLSession.BeamCalendar' (copies BeamCalendar.beamCalendarId to BLSession.beamCalendarId). If this is not the intention, consider if these relationships should be linked with back_populates, or if viewonly=True should be applied to one or more if they are read-only. For the less common case that foreign key constraints are partially overlapping, the orm.foreign() annotation can be used to isolate the columns that should be written towards.   To silence this warning, add the parameter 'overlaps="BeamCalendar"' to the 'BLSession.BeamCalendar1' relationship. (Background on this error at: https://sqlalche.me/e/14/qzyx)

This is a bit strange as the update script should have dropped the old constraint:

ispyb-database/schemas/ispyb/updates/2022_04_12_BLSession_fk_beamCalendarId_set_null.sql

Lines 3 to 6 in 0ccd996

    
           ALTER TABLE BLSession 
        
             DROP CONSTRAINT IF EXISTS BLSession_ibfk_3, 
        
             DROP INDEX IF EXISTS BLSession_ibfk_3, 
        
             ADD CONSTRAINT FOREIGN KEY IF NOT EXISTS BLSession_fk_beamCalendarId (beamCalendarId) REFERENCES BeamCalendar (beamCalendarId) ON DELETE SET NULL ON UPDATE CASCADE;

However, running the update script on a the old version of the table does not drop the constraint, and it also does not produce any warning or error message.

Anyway, a statement ALTER TABLE BLSession DROP CONSTRAINT BLSession_ibfk_3; does the job. I'll push a new update script to do that.

Fixed by cb0a301

	CONSTRAINT `BLSession_fk_beamCalendarId` FOREIGN KEY (`beamCalendarId`) REFERENCES `BeamCalendar` (`beamCalendarId`) ON DELETE SET NULL ON UPDATE CASCADE,
	CONSTRAINT `BLSession_ibfk_1` FOREIGN KEY (`proposalId`) REFERENCES `Proposal` (`proposalId`) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT `BLSession_ibfk_2` FOREIGN KEY (`beamLineSetupId`) REFERENCES `BeamLineSetup` (`beamLineSetupId`) ON DELETE CASCADE ON UPDATE CASCADE,
	CONSTRAINT `BLSession_ibfk_3` FOREIGN KEY (`beamCalendarId`) REFERENCES `BeamCalendar` (`beamCalendarId`)

	ALTER TABLE BLSession
	DROP CONSTRAINT IF EXISTS BLSession_ibfk_3,
	DROP INDEX IF EXISTS BLSession_ibfk_3,
	ADD CONSTRAINT FOREIGN KEY IF NOT EXISTS BLSession_fk_beamCalendarId (beamCalendarId) REFERENCES BeamCalendar (beamCalendarId) ON DELETE SET NULL ON UPDATE CASCADE;