DiamondLightSource / DiamondLightSource/ispyb-database

Circular foreign key references

Open
#66 3 comments 1 reaction 0 assignees View on GitHub
Dominant language
Shell
Stars
7
Forks
13
PR merge metrics
No merged PRs in 30d

Description

Attempting to generate [sqlalchemy](https://sqlalchemy.readthedocs.io/) models via [sqlacodegen](https://pypi.org/project/sqlacodegen/) leads to the following warning/error:
```
sqlacodegen mysql+pymysql://user:password@host:port/ispyb_build --noinflect > models.py
/Users/rjgildea/software/cctbx_py3/conda_base/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py:1213: SAWarning: Cannot correctly sort tables; there are unresolvable cycles between tables "BLSample, BLSampleImage, BLSubSample, DataCollection, Screening, ScreeningOutput, ScreeningStrategy, ScreeningStrategySubWedge, ScreeningStrategyWedge", which is usually caused by mutually dependent foreign key constraints. Foreign key constraints involving these tables will not be considered; this warning may raise an error in a future release.
util.warn(
```

This appears to be the result of two circular foreign key references:
`BLSample -> BLSubSample -> BLSampleImage -> BLSample` (via `BLSample.blSubSampleId`)
and
`DataCollection -> screeningStrategySubWedge -> ScreeningStrategyWedge -> ScreeningStrategy -> Screening -> DataCollection` (via `DataCollection.strategySubWedgeOrigId`).

As far as I can tell, both `BLSample.blSubSampleId` and `DataCollection.strategySubWedgeOrigId` appear to be unused at DLS. I don't understand what purpose either column serves. Can these columns be removed from the table?

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.