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) );
ERROR: relation "bus" does not exist ERROR: relation "route" does not exist ERROR: relation "stop" does not exist ERROR: relation "route" does not exist