PostgreSQL error: relation does not exist

I have created 2 tables in postgres, one of them seems to be fine but the other one returns the error: relation “series” does not exist. These 2 tables are completely the same, except for the primary key and some columns. what seems to be the problem? when the primary key is different, shouldnt that create table? ”’

CREATE TABLE Films
(
Fid varchar (3) NOT NULL DEFAULT '000' PRIMARY KEY,
Uiid varchar (3),
FOREIGN KEY (Uiid) REFERENCES Users(Uid)
ON DELETE SET Default
ON UPDATE CASCADE,

Ciid varchar (3),
FOREIGN KEY (Ciid) REFERENCES Cinemas(Cid)
ON DELETE SET Default
ON UPDATE CASCADE,

Aiid varchar (3),
FOREIGN KEY (Aiid) REFERENCES Actor(Aid)
ON DELETE SET Default
ON UPDATE CASCADE,

Diid varchar (3),
FOREIGN KEY (Diid) REFERENCES Director(Did)
ON DELETE SET Default
ON UPDATE CASCADE,

uComm varchar (15000),
FOREIGN KEY (uComm) REFERENCES UserComments(Commentss)
ON DELETE SET Default
ON UPDATE CASCADE,

FName char (20) NOT NULL,
FprodYear char(4) NOT NULL,
FRate RATE,
FGenre char(50) NOT NULL        
);

”’

CREATE TABLE Series
(
Sid varchar (3) NOT NULL DEFAULT '000' PRIMARY KEY,
Uiiid varchar (3),
FOREIGN KEY (Uiiid) REFERENCES Users(Uid)
ON DELETE SET Default
ON UPDATE CASCADE,
Ciiid varchar (3),
FOREIGN KEY (Ciiid) REFERENCES Cinemas(Cid)
ON DELETE SET Default
ON UPDATE CASCADE,

Aiiid varchar (3),
FOREIGN KEY (Aiiid) REFERENCES Actor(Aid)
ON DELETE SET Default
ON UPDATE CASCADE,

Diiid varchar (3),
FOREIGN KEY (Diiid) REFERENCES Director(Did)
ON DELETE SET Default
ON UPDATE CASCADE,

uCommm varchar (15000),
FOREIGN KEY (uCommm) REFERENCES UserComments(Commentss)
ON DELETE SET Default
ON UPDATE CASCADE,

SName char (20) NOT NULL,
SprodYear char(4) NOT NULL,
SRate RATE,
SGenre char(50) NOT NULL
);

Answer

Before this two tables have you created this tables:

  • Users
  • Cinemas
  • Actor
  • Director
  • UserComments

If you have not then both of this tables will not be created.

Also please check have you created a type called RATE.

If you have already created tables I have mentioned and datatype RATE make sure you have primary key’s in this table so your foreign key’s can reference them.

Then, if you have done all of this do check the comment from @TheImpaler: “DEFAULT ‘000’ on a primary key (or any key) makes little sense.”.

Also, when you have primary key on the column you do not need NOT NULL constraint.

After all of that you will have two codes that work: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a91b77594583e6768360709ef7a9f494

After commenting with the OP I have discovered that he can create the table by referencing the schema before the table name like this:

create table schema_name.Series...

Leave a Reply

Your email address will not be published. Required fields are marked *