Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2)

CREATE TABLE IF NOT EXISTS restaurant
(
id UUID NOT NULL PRIMARY KEY DEFAULT gen_random_uuid(),
title VARCHAR(50) NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS restaurant_gallery
(
id              SERIAL,
restaurant_id   UUID NOT NULL REFERENCES restaurant,
image           VARCHAR NOT NULL,
created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (id, restaurant_id)
);

CREATE TABLE IF NOT EXISTS category
(
id              SERIAL,
restaurant      UUID NOT NULL,
name            VARCHAR(25) NOT NULL,
created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

PRIMARY KEY (id, name)
);

CREATE TABLE IF NOT EXISTS item
(
id              SERIAL,
restaurant_id   UUID NOT NULL REFERENCES restaurant,
category_id     INT NOT NULL REFERENCES category,
name            VARCHAR(25) NOT NULL,
created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

PRIMARY KEY (id, restaurant_id)
);

CREATE TABLE IF NOT EXISTS item_gallery
(
id              SERIAL,
item_id         INT NOT NULL REFERENCES item,
image           VARCHAR NOT NULL,
created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),

PRIMARY KEY (id, item_id)
);

Hey @taufiq,

See this document about creating foreign keys.
Specifically:

In the REFERENCES clause, if you specify a table but no columns, CockroachDB references the table’s primary key. In these cases, the Foreign Key constraint and the referenced table’s primary key must contain the same number of columns.

The problem here is the foreign key in the item table is referencing the primary key on the category table. However, the primary key in the category table is a composite key.

You can do either of the following:

  1. When you create the category table, you can make the primary key composed of one column only (the id column), and then you should be able to create the item table without problems.

  2. You can keep your composite key on the category table, and add REFERENCES category (id) in your category_id definition when creating the item table (You will have to add the UNIQUE clause to the id column in the category table).

Let me know if this solves your problem.

Thanks,
Matt