Why do I get a foreign key not unique error on an identity column?-CodePudding

If I try to create two tables like this in PostgreSQL 14.3:

CREATE TABLE IF NOT EXISTS foo (
  id INT4 GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 1 MINVALUE -2147483648 START -2147483648),
  foo TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS bar (
  id INT4 GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 1 MINVALUE -2147483648 START -2147483648),
  foo_id INT4 NOT NULL REFERENCES foo(id)
);

I get this output:

$ psql test -f bug01.sql
CREATE TABLE
psql:bug01.sql:9: ERROR:  there is no unique constraint matching given keys for referenced table "foo"

I thought GENERATED BY DEFAULT AS IDENTITY would have satisfied the uniqueness constraint. What am I missing? And how do I resolve the issue?

UPDATE: Applying Bergi's answer and comments, I added a primary key constraint to each identity column and that solved the problem:

CREATE TABLE IF NOT EXISTS foo (
  id INT4 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 1 MINVALUE -2147483648 START -2147483648),
  foo TEXT UNIQUE NOT NULL
);

CREATE TABLE IF NOT EXISTS bar (
  id INT4 PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY (INCREMENT BY 1 MINVALUE -2147483648 START -2147483648),
  foo_id INT4 NOT NULL REFERENCES foo(id)
);

$ psql test -f bug01.sql
CREATE TABLE
CREATE TABLE

Thanks!

CodePudding user response：

Well, no. GENERATED just means it's harder to overwrite (requiring special syntax) than a DEFAULT value, and AS IDENTITY is just a fancy way of defining an implicit sequence, similar to SERIAL.

You still can insert duplicates in your column, either by resetting the sequence or using OVERRIDING SYSTEM VALUE, you must declare the appropriate constraints to prevent this. Simply make your id the PRIMARY KEY of the table.