Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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