Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

Re: Foreign key referencing a partitioned table

Thomas Day

2006-09-18

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