I am trying to create tables based on relational schema, but I get "Relation does not exist" errors for some tables and could not find a way to get out of this. I will display my schema, code, and actual errors below. I really feel this might have a simple solution, but I just got stuck into it..
CREATE TABLE Driver
(
Id CHAR(11) NOT NULL,
FirstName VARCHAR(15) NOT NULL,
LastName VARCHAR(20) NOT NULL,
Phone CHAR(12) CHECK (char_length(Phone) > 8),
PRIMARY KEY (Id)
);
CREATE TABLE Driving
(
Id CHAR(11) REFERENCES Driver ON DELETE CASCADE,
BusId INT REFERENCES Bus ON DELETE CASCADE,
PRIMARY KEY (Id, BusId)
);
CREATE TABLE Bus
(
BusId INT NOT NULL,
Make VARCHAR(20) DEFAULT ('Volvo'),
Model VARCHAR(20),
Year SMALLINT NOT NULL CONSTRAINT ManufacturingYear
CHECK ((Year > 1970) AND
(Year < date_part('year', current_date) + 1)),
Space SMALLINT CHECK (Space > 0),
Route SERIAL,
PRIMARY KEY (BusId),
FOREIGN KEY (Route) REFERENCES Route ON DELETE SET NULL
);
CREATE TABLE Route
(
RouteId SERIAL NOT NULL,
StartingPoint SMALLINT NOT NULL,
EndingPoint SMALLINT NOT NULL,
PRIMARY KEY(RouteId),
FOREIGN KEY (StartingPoint) REFERENCES Stop,
FOREIGN KEY (EndingPoint) REFERENCES Stop
);
CREATE TABLE RouteStops
(
Route SERIAL,
Stop SMALLINT,
PRIMARY KEY(Route, Stop),
FOREIGN KEY (Route) REFERENCES Route ON DELETE CASCADE,
FOREIGN KEY (Stop) REFERENCES Stop ON DELETE CASCADE
);
CREATE TABLE Stop
(
StopId SMALLINT NOT NULL,
Name VARCHAR(30) DEFAULT ('Station'),
PRIMARY KEY(StopId)
);
Errors:
ERROR: relation "bus" does not exist
ERROR: relation "route" does not exist
ERROR: relation "stop" does not exist
ERROR: relation "route" does not exist
Best Answer
You try to create relations (constraint) before create parent objects, for example:
order #2:
but table Bus at this moment not created.
2 possible solutions: