Multiple foreign keys

SQL Apprentice Question
I wonder if it is possible to have multiple foreign keys between to tables
and delete data automatically. We have on table with a number of locations
and another table with a number of routes between them. The foreign keys go
from location to the beginning and the end of the route. How can I secure
that (1) only data from location is used in route and (2) if a dataset in
location is deleted both routes to and from are deleted. I can't have a
cascading key on both relations and i couldn't make a trigger work either.
Any suggestions?


Celko Answers
Technically, this is okay in Standard SQL because of the constraints
that force one and only one execution path.

CREATE TABLE Locations
(location_id INTEGER NOT NULL PRIMARY KEY,
location_name VARCHAR(30) NOT NULL);


CREATE TABLE Routes
(route_name VARCHAR(30) NOT NULL PRIMARY KEY,
start_location_id INTEGER NOT NULL
CONSTRAINT starting
REFERENCES Locations (location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
final_location_id INTEGER NOT NULL
CONSTRAINT ending
REFERENCES Locations (location_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
UNIQUE(start_location_id, final_location_id),
CHECK (start_location_id <> final_location_id));


A smart SQL engine will detect that {ending, starting} and {starting,
ending} will give the same results. This would be legal and deleting a
location removing a node from a graph -- the edges would also
disappear.