Foreign key referencing a partitioned table
- From: "Thomas Day" <tomday2@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Mon, 18 Sep 2006 12:55:54 -0400
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:
