transit pattern links table structure

The Transit_Pattern_Links table holds the information on the sequence of of transit links that are traversed by each transit pattern

Table Structure

Field	Type	NULL allowed	Default Value	Foreign key	Description
pattern_id	INTEGER	NO		Transit_Patterns(pattern_id)	ID of the pattern in the format AARRRRPPPP0000 (Agency, Route, Pattern)
index	INTEGER	NO			Sequence number of the link served by the pattern
transit_link	INTEGER	NO		Transit_Links(transit_link)	ID of the transit link that the pattern serves at the given “index” sequence

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE IF NOT EXISTS "Transit_Pattern_Links" (
    pattern_id     INTEGER    NOT NULL,
    "index"        INTEGER    NOT NULL,
    transit_link   INTEGER    NOT NULL,

    FOREIGN KEY(pattern_id) REFERENCES "Transit_Patterns"(pattern_id) deferrable initially deferred,
    FOREIGN KEY(transit_link) REFERENCES "Transit_Links"(transit_link) deferrable initially deferred
);

create UNIQUE INDEX IF NOT EXISTS transit_pattern_links_stop_id ON Transit_Pattern_Links (pattern_id, transit_link);