| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Foreign key referencing a partitioned table
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 ,
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,
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_at_perceptron.com> 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_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-lReceived on Mon Sep 18 2006 - 12:26:33 CDT
 |
 |