Search code examples
sqlpostgresqlforeign-keyscreate-table

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)
)
Solution

  • 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