I have a big table referenced by other tables and need to split the table on several parts.

  1. I create table by CREATE TABLE product_part0 () INHERITS (product);
  2. Move some rows to product_part0 by INSERT INTO product_part0 SELECT * FROM ONLY product ORDER BY id LIMIT 200;
  3. Now I need to remove duplicates from master-table. Run DELETE FROM ONLY product WHERE id IN (SELECT id FROM product_part0);

With result:

ERROR: update or delete on table "product" violates foreign key constraint
"product_id_refs" on table "searchcache"
DETAIL: Key (id)=(13375) is still referenced from table "searchcache".

So, how to delete duplicate rows?

I made similar question at postgresql maillist.


Unfortunately, you can't define a foreign key against a partitioned table, only the partitions themselves - but, of course, the referencing table can have only on FK on the column in question...

So you have to either partition the referencing table, too, or live together with foreign-keylessness (with help from a trigger or the application code).

Let me note that similarly, you will have to define the eventual (unique) indexes on the child tables one by one. This also means that unique constraints have to be defined this way, too.

  • Trigger integrity is reliable? Can u tutorial link? – Vladislav Sep 20 '13 at 4:24
  • how about performance issues when using triggers instead of foreign keys? – kasia.b Sep 20 '13 at 10:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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