Re: On partitioning, PKs and FKs

Ron <ronljohnsonjr@xxxxxxxxx> · Thu, 8 Jul 2021 06:30:18 -0500



    On 7/8/21 3:42 AM, Wiwwo Staff wrote:

    
        Hi!
        I have a big
          table bigTable
          which I partitioned by hash on field columnX, by creating bigTable_0, bigTable_1 etc.
        
        Since I need a
          PK on bigTable.id, and table is not partitioned by
              id, and columnX is not
              unique, i added PK on bigTable_0.id, bigTable_1.id etc. So
                    far, so good.
        

        Now I have anotherTable,
                    which has column bigTable_id
                    referencing bigTable.id.
        Creating FK
                        anotherTable__bigTable_FK
                                                          on
                        anotherTable.bigTable_id -you guess :D-
                                      fails with 

                                  
          [42830]
                                      ERROR: there is no unique
                                      constraint matching given keys for
                                      referenced table "bigTable"
        
        Also
                                      creating a FK pointing to one of
                                      the hash tables bigTable_0.id,
                                          bigTable_1.id
                                              etc. also fails with
        
          ERROR:
               23503: insert or update on table "anotherTable" violates
              foreign key constraint "anotherTable__bigTable_FK".
        
        
        Do
                                                  I have a way out? :)

                                              
https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

    
    Section "5.11.2.3. Limitations" says, "Unique
      constraints (and hence primary keys) on partitioned tables must
      include all the partition key columns."

    
    Thus, the bigTable PK must be on id, columnX, 
    (No, I don't like it either.)

    
    -- 

      Angular momentum makes the world go 'round.