postgresql: relation "table_name" does not exist when trying to CREATE TABLE

Question

I am running this code thru pgAdmin4:

CREATE TABLE product(
    code integer, 
    pname varchar(30),
    descr varchar(50),
    utype varchar(30),
    uprice float, 
    manu varchar(30),
    sid integer,
    PRIMARY KEY (code, sid),
    FOREIGN KEY (sid) REFERENCES supplier(sid)
);

CREATE TABLE branch(
    bid integer,
    bname varchar(30),
    baddress varchar(50),
    PRIMARY KEY (bid)
);

CREATE TABLE stock(
    code integer,
    bid integer,
    units float,
    PRIMARY KEY (code, bid)
);

CREATE TABLE receipt(
    bid integer,
    rdate date,
    rtime time,
    ptype varchar(30),
    total float,
    PRIMARY KEY (bid, rdate, rtime)
);

CREATE TABLE purchase(
    bid integer,
    rdate date,
    rtime time,
    code integer,
    units float,
    PRIMARY KEY (bid, rdate, rtime, code),
    check (units > 0)
);

CREATE TABLE supplier(
    sid integer,
    sname varchar(30),
    address varchar(50),
    phone numeric (9,0),
    PRIMARY KEY (sid)
);

INSERT INTO product (code, pname, descr, utype, uprice, manu, sid) VALUES
        (987, 'Tomatoes',       'Vegetable',  'Kg',  5.99,  'manufacturer1', 111),
        (876, 'Cucumbers',      'Vegetable',  'Kg',  4.99,  'manufacturer1', 222),
        (765, 'Cornflakes',     'Cornflakes', 'Box', 15.9,  'manufacturer1', 222),

INSERT INTO branch (bid, bname, baddress) VALUES
        (987, 'tal aviv', 'road 1 tel aviv'),
        (878, 'Raanana',  'road 1 raanana'),
        (767, 'Holon',    'road 1 holon');

INSERT INTO stock (code, bid, units) VALUES
        (987, 989, 50),
        (987, 878, 75),
        (987, 767, 100),

INSERT INTO receipt (bid, rdate, rtime, ptype) VALUES
        (989, '2020-3-19', '10:00', 'Cash'),
        (989, '2020-7-16', '12:30', 'Credit'),
        (989, '2020-7-15', '15:35', 'Credit'),

INSERT INTO purchase (bid, rdate, rtime, code, units) VALUES
        (989, '2020-3-18', '10:00', 987, 5),
        (989, '2020-3-18', '10:00', 876, 3),
        (989, '2020-3-18', '10:00', 543, 4),

INSERT INTO supplier (sid, sname, address, phone) VALUES
        (111, 'supplier2', 'road2 tel aviv',  111111111),
        (222, 'supplier3', 'road3 jerusalem', 222222222),
        (333, 'supplier4', 'road2 eilat',     333333333);

i am keep getting this message: ERROR: relation "supplier" does not exist SQL state: 42P01

The problem is with the foreign key in the product table (1st table), i know this because i removed it and it created all the tables with values (i removed some of the values to facilitate the writing).

Not sure if its a syntax error, i tried a couple of ways to solve it but i am clueless, any suggestions? Thanks in advance.

Obviously you need to create the supplier table before you can define a foreign key on it. Re-order your statements. — user330315
– user330315, Commented Jul 15, 2020 at 21:56
@a_horse_with_no_name i tried a that option but it keeps consisting that the table does not exsits, you suggest maybe after all the values and tables are created to add that command? — Cykapath
– Cykapath, Commented Jul 16, 2020 at 5:43

zealous · Accepted Answer · 2020-07-15 21:58:56Z

2

You have to create supplier table before product table as sid from product table references to sid in supplier table as a foreign key.

Here is the working demo.

CREATE TABLE supplier(
    sid integer,
    sname varchar(30),
    address varchar(50),
    phone numeric (9,0),
    PRIMARY KEY (sid)
);

CREATE TABLE product(
    code integer, 
    pname varchar(30),
    descr varchar(50),
    utype varchar(30),
    uprice float, 
    manu varchar(30),
    sid integer,
    PRIMARY KEY (code, sid),
    FOREIGN KEY (sid) REFERENCES supplier(sid)
);

answered Jul 15, 2020 at 21:58

zealous

7,5434 gold badges20 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Cykapath Over a year ago

works perfectly thanks, i used the demo for that. In my previous attemps i tried to switch the product and supplier locations but not to put them both lower in the sql query, and thats why i kept getting that error.

Rajat · Accepted Answer · 2020-07-15 22:03:50Z

2

One option is to add the constraint later (before the insert). This way you don't have to worry about the order of table creation.

ALTER TABLE product
ADD FOREIGN KEY (sid) REFERENCES supplier(sid);

answered Jul 15, 2020 at 22:03

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

postgresql: relation "table_name" does not exist when trying to CREATE TABLE

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related