RE: Foreign key referencing a partitioned table

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Mon, 18 Sep 2006 13:11:59 -0400
Message-ID: <F4C27E77F7A33E4CA98C19A9DC6722A2013DE2BD@EXCHANGE.corp.perceptron.com>

This has nothing to do with partitioning. Your problem is, that in the database FK should reference the "whole" PK - not part of it.  

Igor

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Thomas Day Sent: Monday, September 18, 2006 12:56 PM To: Oracle-L_at_freelists.org
Subject: Foreign key referencing a partitioned table

Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.7.0 - Production NLSRTL Version 9.2.0.7.0 - Production  

I'm building a partitioned table that has a surrogate key and a date as it's primary key. I'm partitioning the table on the date in the primary key. I then create a global unique index on the surrogate key. But when I try to foreign key to the surrogate key I get -  

ORA-02270: no matching unique or primary key for this column-list  

If I drop the date from the primary key (and not range partition the table) then the foreign key works fine. I've spent the last hour banging my head against the fine Oracle documentation and have the feeling that I'm looking in the wrong places and missing something increadibly simple. Anyone have any ideas?  

This works OK  

CREATE TABLE ADDR_DIMENSION
(

 ADDR_ID NUMBER(10) DEFAULT 1 NOT NULL ,  ASSOCIATION_BEGIN_DATE DATE NOT NULL ,  ADDR_TYPE_CODE VARCHAR2(3) NULL ,
 ADDR_TYPE_NAME VARCHAR2(80) NULL ,

 STREET_LINE_1_ADDR  VARCHAR2(60)  NULL , 
 STREET_LINE_2_ADDR  VARCHAR2(60)  NULL ,
 STREET_LINE_3_ADDR  VARCHAR2(40)  NULL ,

 APARTMENT_NBR VARCHAR2(10) NULL ,
 PO_BOX_NBR VARCHAR2(9) NULL ,
 CITY_NAME VARCHAR2(60) NULL ,
 SUBDIVISION_CODE VARCHAR2(3) NULL ,
 SUBDIVISION_NAME VARCHAR2(80) NULL ,
 CNTRY_CODE VARCHAR2(5) NULL ,
 CNTRY_NAME VARCHAR2(80) NULL ,
 POSTAL_CODE VARCHAR2(24) NULL ,

 STD_CITY_NAME  VARCHAR2(60)  NULL ,
 STD_CNTRY_CODE  VARCHAR2(5)  NULL , 
 STD_SUBDIVISION_NAME  VARCHAR2(80)  NULL ,
 STD_CNTRY_NAME  VARCHAR2(80)  NULL ,
 STD_POSTAL_CODE  VARCHAR2(10)  NULL ,
 STD_FIPS_STATE_CODE  VARCHAR2(2)  NULL ,
 STD_ISO_STATE_CODE  VARCHAR2(2)  NULL ,

 ASSOCIATION_END_DATE DATE NULL,
  constraint ADDR_DIM_PK PRIMARY KEY (   addr_id))
 partition by hash (ADDR_ID)

            (PARTITION a,
             PARTITION b,
             PARTITION c

 );  

ALTER TABLE ADDR_FACT
 ADD (CONSTRAINT ADDR_FACT_ADDR_FK FOREIGN KEY (ADDR_ID) REFERENCES ADDR_DIMENSION(ADDR_ID));     This ddl doesn't -  

CREATE TABLE ADDR_DIMENSION
(

 ADDR_ID NUMBER(10) DEFAULT 1 NOT NULL ,  ASSOCIATION_BEGIN_DATE DATE NOT NULL ,  ADDR_TYPE_CODE VARCHAR2(3) NULL ,
 ADDR_TYPE_NAME VARCHAR2(80) NULL ,

 STREET_LINE_1_ADDR  VARCHAR2(60)  NULL , 
 STREET_LINE_2_ADDR  VARCHAR2(60)  NULL ,
 STREET_LINE_3_ADDR  VARCHAR2(40)  NULL ,

 APARTMENT_NBR VARCHAR2(10) NULL ,
 PO_BOX_NBR VARCHAR2(9) NULL ,
 CITY_NAME VARCHAR2(60) NULL ,
 SUBDIVISION_CODE VARCHAR2(3) NULL ,
 SUBDIVISION_NAME VARCHAR2(80) NULL ,
 CNTRY_CODE VARCHAR2(5) NULL ,
 CNTRY_NAME VARCHAR2(80) NULL ,
 POSTAL_CODE VARCHAR2(24) NULL ,

 STD_CITY_NAME  VARCHAR2(60)  NULL ,
 STD_CNTRY_CODE  VARCHAR2(5)  NULL , 
 STD_SUBDIVISION_NAME  VARCHAR2(80)  NULL ,
 STD_CNTRY_NAME  VARCHAR2(80)  NULL ,
 STD_POSTAL_CODE  VARCHAR2(10)  NULL ,
 STD_FIPS_STATE_CODE  VARCHAR2(2)  NULL ,
 STD_ISO_STATE_CODE  VARCHAR2(2)  NULL ,

 ASSOCIATION_END_DATE DATE NULL,
  constraint ADDR_DIM_PK PRIMARY KEY (   addr_id,ASSOCIATION_BEGIN_DATE))
 partition by range(ASSOCIATION_BEGIN_DATE)

            (PARTITION FY2004 VALUES LESS THAN ('1-OCT-2004'), 
             PARTITION FY2005 VALUES LESS THAN ('1-OCT-2005'),
             PARTITION FY_CURRENT VALUES LESS THAN (MAXVALUE)

 );  

CREATE UNIQUE INDEX ADDR_DIM_ADDR_UK_I ON ADDR_DIMENSION(ADDR_ID);    ALTER TABLE ADDR_FACT
  ADD (CONSTRAINT ADDR_FACT_ADDR_FK FOREIGN KEY (ADDR_ID) REFERENCES  ADDR_DIMENSION(ADDR_ID));   ADDR_DIMENSION(ADDR_ID))
               *
ERROR at line 3:
ORA-02270: no matching unique or primary key for this column-list    

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Sep 18 2006 - 12:11:59 CDT