Only raise a foreign key violation error when network_id is missing from both networks.txt and routes.txt

[qcdyx]

Describe the problem

The network_id field of GtfsFareLegRule is a foreign key that references routes.txt. If should reference both routes.txt and networks.txt. cc @tzujenchanmbd

Proposed solution

networkId is declared as a foreign key that references the network_id field in both the routes.txt and networks.txt tables.

Logic:

IF network_id exists in fare_leg_rules.txt but not in routes.txt OR networks.txt, generate a foreign_key_violation notice.

Alternatives you've considered

No response

Additional context

No response

[emmambd]

Answers to some questions @davidgamez @qcdyx shared on this issue:

• Is network_id is always unique, across both routes.network_id and networks.network_id ? (Yes, since we already have a rule that triggers if it's defined in 2 places)

• Could network_id be defined in outside of routes.txt and networks.txt? No, it's only defined in those two files.

Based on this, do you need anything else clarified before I rewrite the issue to specify 1. we need to change foreign_key_violation to omit network_id 2. we need to add a new rule to check this conditional?

[emmambd]

Closing #1764 as duplicate of #1756

[Alessandro100]

Tasks [] Only raise a foreign key violation error when network_id is missing from both networks.txt and routes.txt [] Review auto generated documentation and add / update if needed [] Test with (test GTFS.zip) [] Review acceptance tests