Question 177194 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – why references not working when using dbschema tool?

I use DBSCHEMA tool and want to execute a query but I got this error:

syntax error at or near "id"
position 209

Code:

CREATE TABLE user_info (
  id UUID PRIMARY KEY,
  full_name VARCHAR NOT NULL,
  birthday VARCHAR NOT NULL,
  sex VARCHAR NOT NULL,
  profile_image VARCHAR,
  verified SMALLINT DEFAULT 0,
  legend VARCHAR NULL,
  FOREIGN KEY id REFERENCES user(id)
)
Tags:

2

Answers


  1. 0

    According to the documentation the column(s) defined for the constraint should be in parenthesis; in addition, user should be delimited.

    FOREIGN KEY (id) REFERENCES "user"(id)

    fiddle demo

    Login or Signup to reply.
  2. 0

    There are several problems with your code.

    First, this is not valid SQL:

    FOREIGN KEY id REFERENCES user(id)

    The column(s) needs to be surrounded with parenthesis (keep in mind that a foreign key could involve more than one column):

    FOREIGN KEY (id) REFERENCES user(id)

    Then: user is a reserved word in Postgres, so it needs be quoted when used as an identifier – which has the side effect of making the identifier case-sensitive. Assuming that you created this table as all lower-case, you would do:

    FOREIGN KEY (id) REFERENCES "user"(id)

    But it would really be a better idea not to use a reserved word as table name: I would recommend renaming the table to something else if you have a chance to.

    DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search