Why PSQL create table returns [42P01] ERROR: relation does not exist

I’m new to SQL. I was trying to run sql schema, here is a part of code

create table PageColours (
    id          serial,
    userID      serial references  users(id),
                --references users(id),
    isTemplate  boolean default'false',
    name        NameValue not null unique,
    primary key (id)
);

create table People (
    id          serial,
    email       EmailValue not null unique,
    givenName   NameValue not null,
    familyName  NameValue,
    invitedID   serial references Events(id),
    attendedID  serial references Events(id),

    primary key (id)
);

create table users(
  id        serial
            references People(id),
    passWord    varchar not null,
   
    BillingAddress  serial not null
                    references Places(id),
    HomeAddress     serial
                    references Places(id),
    ListID          serial
                    references ContactLists(id),
    ColorID         serial
                    references PageColours(id),
    primary key (id)

);

It returns[2020-07-03 15:28:19] [42P01] ERROR: relation "people" does not exist

[2020-07-03 15:28:19] [42P01] ERROR: relation "users" does not exist

In fact all foreign key reference table reference not exist. When i remove the reference, the table can be created, can someone please help me ?

Answer

The script is run sequentially. So when the pagecolours table is created, the users table does not yet exist and thus the references users fails.

You need to re-order the script, so that the users table is created first. But as you have a circular reference (users references pagecolours and pagecolours references users) you need to create the tables without an “inline” reference, and then at the end of the script you need to run an ALTER TABLE to create the foreign keys.

But having a circular reference like that, is usually not a good idea. But if you are 100% sure you need it, you should at least declare the foreign keys as deferrable, to make inserting rows easier.

---

Also: serial is not a datatype. A foreign key column that references a serial column should be defined as an integer. In general it is recommended to move away from serial and use integer generated always as identity instead.