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?