0

I have a few tables and I am facing this issue most likely with the table "item". When I remove restaurant_id and category_id, it works fine.

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

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

CREATE TABLE IF NOT EXISTS category
(
    id              SERIAL NOT NULL PRIMARY KEY,
    restaurant      UUID NOT NULL,
    label           VARCHAR(25) NOT NULL,

    UNIQUE (restaurant, label)
);

CREATE TABLE IF NOT EXISTS item
(
    id              SERIAL NOT NULL PRIMARY KEY,
    restaurant_id   UUID NOT NULL REFERENCES restaurant (id),
    category_id     SERIAL NOT NULL REFERENCES category (id),
    name            VARCHAR(25) NOT NULL,
    UNIQUE (id, restaurant_id)
);

CREATE TABLE IF NOT EXISTS item_gallery
(
    id              SERIAL,
    item_id         INT NOT NULL REFERENCES item,
    image           VARCHAR NOT NULL,
    PRIMARY KEY (id, item_id)
);

Error:

pq: 1 columns must reference exactly 1 columns in referenced table (found 2)
Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2)
Failed running "sql"

Any help is highly appreciated!

closed as unclear what you're asking by mustaccio, kevinsky, Marco, Colin 't Hart, MDCCL Aug 12 at 23:08

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • Why is name part of the primary key in category? That seems weird and is the cause of the error. – Laurenz Albe Aug 12 at 11:12
  • Makes sense. I actually wanted to INDEX these keys since I would query a lot with these... – Inconnu Aug 12 at 11:14
  • That is too little information. Can you edit the question and adapt the table definitions to your changes? Otherwise I have no idea what you are doing. If you changed the primary key for category to id, the error message can no longer be columns must reference exactly 1 columns in referenced table. – Laurenz Albe Aug 12 at 11:25
  • @LaurenzAlbe The final change has worked for me. You can see in the edit. Changing PK() to UNIQUE() for the item table... thank you... – Inconnu Aug 12 at 11:36
  • 1
    Please do not change the question. You can revert to its first revision and post the good code that works as an answer. – ypercubeᵀᴹ Aug 12 at 11:43
1

For a foreign key, you need to tell Postgres which table you want to refer to and the name(s) of the field(s) within that table that you want to use as the foreign key(s):

CREATE TABLE IF NOT EXISTS item
(
    id              SERIAL,
    restaurant_id   UUID         NOT NULL  REFERENCES restaurant ( id ) 
    category_id     INT          NOT NULL  REFERENCES category ( id ) 
    name            VARCHAR(25)  NOT NULL,
    PRIMARY KEY (id, restaurant_id)
);
  • This is giving me pq: there is no unique constraint matching given keys for referenced table category Error: pq: there is no unique constraint matching given keys for referenced table category Failed running "sql" – Inconnu Aug 12 at 11:09
  • Perhaps because your category table has no Primary Key ("unique constraint") for the Foreign Key to refer to? – Phill W. Aug 12 at 11:19
  • Added it, and I'm back to the original problem... – Inconnu Aug 12 at 11:21
  • 1
    Your item_gallery table is attempting to reference your item table but this has a Primary key on "(id, restaurant_id)". You need to include both of these in the Foreign Key from item-gallery. – Phill W. Aug 12 at 11:38
  • The problem has been solved. Check the comment section. – Inconnu Aug 12 at 11:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.