Re: Foreign key referencing a partitioned table

  • From: "Thomas Day" <tomday2@xxxxxxxxx>
  • To: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
  • Date: Mon, 18 Sep 2006 13:26:33 -0400

But I do have a unique index on just that column.  Shouldn't it be able to
use that?

D'uh - I KNEW it was something simple.  I had the unique index but I forgot
the unique constraint.


SQL> drop table addr_dimension;

Table dropped.

SQL> CREATE TABLE ADDR_DIMENSION
 2  (
 3   ADDR_ID  NUMBER(10)   DEFAULT  1 NOT NULL ,
 4   ASSOCIATION_BEGIN_DATE  DATE  NOT NULL ,
 5   ADDR_TYPE_CODE  VARCHAR2(3)  NULL ,
 6   ADDR_TYPE_NAME  VARCHAR2(80)  NULL ,
 7   STREET_LINE_1_ADDR  VARCHAR2(60)  NULL ,
 8   STREET_LINE_2_ADDR  VARCHAR2(60)  NULL ,
 9   STREET_LINE_3_ADDR  VARCHAR2(40)  NULL ,
10   APARTMENT_NBR  VARCHAR2(10)  NULL ,
11   PO_BOX_NBR  VARCHAR2(9)  NULL ,
12   CITY_NAME  VARCHAR2(60)  NULL ,
13   SUBDIVISION_CODE  VARCHAR2(3)  NULL ,
14   SUBDIVISION_NAME  VARCHAR2(80)  NULL ,
15   CNTRY_CODE  VARCHAR2(5)  NULL ,
16   CNTRY_NAME  VARCHAR2(80)  NULL ,
17   POSTAL_CODE  VARCHAR2(24)  NULL ,
18   STD_CITY_NAME  VARCHAR2(60)  NULL ,
19   STD_CNTRY_CODE  VARCHAR2(5)  NULL ,
20   STD_SUBDIVISION_NAME  VARCHAR2(80)  NULL ,
21   STD_CNTRY_NAME  VARCHAR2(80)  NULL ,
22   STD_POSTAL_CODE  VARCHAR2(10)  NULL ,
23   STD_FIPS_STATE_CODE  VARCHAR2(2)  NULL ,
24   STD_ISO_STATE_CODE  VARCHAR2(2)  NULL ,
25   ASSOCIATION_END_DATE  DATE  NULL,
26    constraint   ADDR_DIM_PK PRIMARY KEY (
27   addr_id,ASSOCIATION_BEGIN_DATE),
28  constraint addr_dim_uk unique(addr_id));

Table created.

SQL> ALTER TABLE ADDR_FACT
 2   ADD (CONSTRAINT  ADDR_FACT_ADDR_FK FOREIGN KEY (ADDR_ID) REFERENCES
 3  ADDR_DIMENSION(ADDR_ID));

Table altered.
Thanks (sincerely) for making me take a look at the obvious.


On 9/18/06, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:

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: