RE: Foreign key referencing a partitioned table

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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Thomas Day
Sent: Monday, September 18, 2006 12:56 PM
To: Oracle-L@xxxxxxxxxxxxx
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
 
 

Other related posts: