What's wrong with the following SQL Create table query?

Using Postgresql

CREATE TABLE IF NOT EXISTS default_collections(
  id SERIAL PRIMARY KEY,
  address VARCHAR,
  collection_id uuid,
  FOREIGN KEY collection_id REFERENCES collections(id)
);

This is what I see on DBeaver:

  Position: 129
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:133)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:513)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$0(SQLQueryJob.java:444)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:431)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:816)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:3477)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:118)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:171)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:116)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:4810)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:105)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "collection_id"
  Position: 129
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322)
    at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:330)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)
    ... 12 more

This is the error I see on EVERSQL:

You have an error in your SQL syntax; it seems the error is around: 'uuid, FOREIGN KEY collection_id REFERENCES collections(id) )' at line 4

Solution 1:

You forgot the parentheses.

The syntax for FOREIGN KEY requires them.

CREATE TABLE IF NOT EXISTS default_collections(
  id SERIAL PRIMARY KEY,
  address VARCHAR,
  collection_id uuid,
  FOREIGN KEY (collection_id) REFERENCES collections(id)
);