Postgresql – Foreign Key to Multicolumn Primary Key

foreign keypostgresql

I have three connected table in my design. Basic logic is when a category deleted, also delete records in category_product table which has deleted category's id in its cat_id column. But when I tried to create tables with query below, I get this error: "there is no unique constraint matching given keys for referenced table 'category_product'". I don't know what is wrong with this design or logic.

CREATE TABLE IF NOT EXISTS category_product (
    cat_id integer NOT NULL,
    product_id integer NOT NULL,
    CONSTRAINT PK_CATEGORY_PRODUCT_ID PRIMARY KEY (cat_id, product_id)
);


CREATE TABLE IF NOT EXISTS categories (
    id serial,
    name text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    CONSTRAINT PK_CATEGORIES_ID PRIMARY KEY (id),
    CONSTRAINT FK_PRODUCTS FOREIGN KEY (id) REFERENCES category_product(cat_id) ON UPDATE NO ACTION ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS products (
    id serial,
    name text NOT NULL,
    description text NULL,
    photo text NULL,
    price money NOT NULL,
    is_in_menu boolean NOT NULL DEFAULT true,
    is_from_kitchen boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT PK_PRODUCTS_ID PRIMARY KEY (id),
    CONSTRAINT FK_CATEGORY_PRODUCT FOREIGN KEY (id) REFERENCES category_product(product_id) ON UPDATE NO ACTION ON DELETE CASCADE
);

So what kind of structure I need to achieve what I am trying?

Best Answer

You need FKs in category_product table, not in categories and products:

CREATE TABLE IF NOT EXISTS categories (
    id serial,
    name text NOT NULL,
    is_active boolean NOT NULL DEFAULT true,
    CONSTRAINT PK_CATEGORIES_ID PRIMARY KEY (id)

);

CREATE TABLE IF NOT EXISTS products (
    id serial,
    name text NOT NULL,
    description text NULL,
    photo text NULL,
    price money NOT NULL,
    is_in_menu boolean NOT NULL DEFAULT true,
    is_from_kitchen boolean NOT NULL DEFAULT true,
    is_deleted boolean NOT NULL DEFAULT false,
    CONSTRAINT PK_PRODUCTS_ID PRIMARY KEY (id)    
);

CREATE TABLE IF NOT EXISTS category_product (
    cat_id integer NOT NULL,
    product_id integer NOT NULL,
    CONSTRAINT PK_CATEGORY_PRODUCT_ID PRIMARY KEY (cat_id, product_id),
    CONSTRAINT FK_CATEGORY_PRODUCT_CATEGORY FOREIGN KEY(cat_id) REFERENCES categories(id)
    ON DELETE CASCADE,
    CONSTRAINT FK_CATEGORY_PRODUCT_PRODUCT FOREIGN KEY(product_id) REFERENCES products(id)
);
Related Question