0

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.. Relational schema

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
2

You try to create relations (constraint) before create parent objects, for example:

order #2:

CREATE TABLE Driving 
(
Id           CHAR(11)         REFERENCES Driver ON DELETE CASCADE,
BusId        INT              REFERENCES Bus    ON DELETE CASCADE,

PRIMARY KEY  (Id, BusId)
);

but table Bus at this moment not created.

2 possible solutions:

  1. Create tables in order - from parent to child (not always possible)
  2. Create all tables without constraints and add all of them after
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.