0

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
);
4
  • 1
    DEFAULT '000' on a primary key (or any key) makes little sense. Commented Jan 26, 2020 at 20:19
  • Please post the exact error message. Also, I imagine, is the second query that fails, right? Commented Jan 26, 2020 at 20:22
  • Stupid idea: could it be that series is a reserved keyword? But yes, error message will tell us exact cause Commented Jan 26, 2020 at 20:25
  • @TheImpaler yes the series table fails, ERROR: relation "series" does not exist SQL state: 42P01 Commented Jan 26, 2020 at 20:28

1 Answer 1

2

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...
Sign up to request clarification or add additional context in comments.

12 Comments

CREATE DOMAIN RATE int DEFAULT 0 CHECK (VALUE IN('0','1','2','3','4','5'));
this is my Rate at the top of the code, I changed the keys to different defaults and I have created those tables beforehands
The error is probably related to another issue that is not shown in the question by the OP. Your SQLFiddle demonstrates the queries are good as far as we can tell. +1
Hi @Dorian well sometimes it is like that, you fix one error to discover others... Let's hope we are moving in right direction... I have started a chat, please can you join ?
Hi @Dorian , you have asked a help and I provided you help with your error. It would be nice to get some feedback from you. You have told me that this answer did help you so it would be nice to get an up vote and also that you accept my answer as correct. In the chat we can see other errors and I can try to help. If I can not you can open a new question...
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.