In GTFS, the field parent_station
is either from stop_id
(self-reference) or NULL
. For instance (note that parent_station
might be NULL
),
SELECT stop_id, stop_name, parent_station FROM stops where stop_id='1970324837184617' OR parent_station='1970324837184617';
+------------------+----------------------+------------------+
| stop_id | stop_name | parent_station |
+------------------+----------------------+------------------+
| 1970324837184617 | Saint Agne-SNCF | |
| 3377699720880648 | Saint Agne-SNCF | 1970324837184617 |
| 3377699720880649 | Saint Agne-SNCF | 1970324837184617 |
| 3377699722011100 | Saint Agne-SNCF | 1970324837184617 |
| 3377699722011101 | Saint Agne-SNCF | 1970324837184617 |
| 3377699722914835 | Saint Agne Gare SNCF | 1970324837184617 |
+------------------+----------------------+------------------+
6 rows in set (0,01 sec)
I create a self-referencing table with:
CREATE TABLE `stops` (
stop_id VARCHAR(255) NOT NULL PRIMARY KEY,
stop_code VARCHAR(255),
stop_name VARCHAR(255),
stop_desc VARCHAR(255),
stop_lat DECIMAL(8,6),
stop_lon DECIMAL(8,6),
location_type INT(2),
parent_station VARCHAR(255),
-- the field `parent_station` might be blank (NULL)
FOREIGN KEY parent_station REFERENCES stops(stop_id) -- self-referential table fields
);
but encounter the issue,
ERROR 1064 (42000) at line 99: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY parent_station REFERENCES stops(stop_id)
)' at line 11
With the help of @Awita, I correct the syntax and the table is created successfully.
FOREIGN KEY (parent_station) REFERENCES stops(stop_id)
But a new issue occurs while loading local data.
LOAD DATA LOCAL INFILE 'stops.txt' INTO TABLE stops FIELDS TERMINATED BY ',' IGNORE 1 LINES;
The error:
ERROR 1452 (23000) at line 140: Cannot add or update a child row: a foreign key constraint fails (`paris_gtfs`.`stops`, CONSTRAINT `stops_ibfk_1` FOREIGN KEY (`parent_station`) REFERENCES `stops` (`stop_id`))
This issue is caused by NULL parent_station
?
It's a simple syntax error. Try this:
FOREIGN KEY (parent_station) REFERENCES stops(stop_id)
I tried to add the foreign key after the table has been created with this command and it works:
ALTER TABLE stops
ADD FOREIGN KEY (parent_station)
REFERENCES stops(stop_id)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With