Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012
    Posts
    14

    Unanswered: ora-02270 no matching unique or primary key for this column-list

    this is my table list and key list created. i am getting this error. please clearify..


    create table TSTSUN17OCT.table1
    (

    ACC_YEAR_CODE VARCHAR2(10 BYTE),
    PERIOD_NBR NUMBER(5),
    COMBINATION_ID NUMBER(10),
    OPEN_DEBIT_BALANCE NUMBER(15,3),
    CUMULATIVE_DEBIT NUMBER(15,3),
    CUMULATIVE_CREDIT NUMBER(15,3)


    )

    ALTER TABLE TSTSUN17OCT.table1 ADD (
    PRIMARY KEY
    (ACC_YEAR_CODE, PERIOD_NBR, COMBINATION_ID)
    USING INDEX
    TABLESPACE TSTSUN17OCT
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    STORAGE (
    INITIAL 4M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    PCTINCREASE 0
    ));


    CREATE TABLE TSTSUN17OCT.table_2
    (
    COMBINATION_ID NUMBER(10),
    ACCOUNT_CODE VARCHAR2(10 BYTE),
    PARTY_ID NUMBER(10),
    PRODUCT_TYPE VARCHAR2(10 BYTE),
    PRODUCT_ID NUMBER(8),
    CURRENCY_CODE VARCHAR2(4 BYTE),
    CREATED_BY NUMBER(8),
    CREATED_DATE DATE
    )


    CREATE TABLE TSTSUN17OCT.table_3
    (
    ACC_YEAR_CODE VARCHAR2(10 BYTE),
    PERIOD_NBR NUMBER(5),
    PERIOD_DESCR VARCHAR2(64 BYTE),
    START_DATE DATE,
    END_DATE DATE,
    STATUS CHAR(1 BYTE),
    CREATED_BY NUMBER(8),
    CREATED_DATE DATE,
    LAST_UPD_BY NUMBER(8),
    LAST_UPD_DATE DATE
    )


    ALTER TABLE TSTSUN17OCT.table1 ADD (
    FOREIGN KEY (COMBINATION_ID)
    REFERENCES TSTSUN17OCT.table_2 (COMBINATION_ID),
    FOREIGN KEY (ACC_YEAR_CODE, PERIOD_NBR)
    REFERENCES TSTSUN17OCT.table_3 (ACC_YEAR_CODE,PERIOD_NBR));

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    you can't define an a primary key constraint and tell it to use an existing index when you haven't made the index it's using yet. create a unique index after the table create.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    In addition, the primary key/unique constraints have to be created on referenced tables/columns. It makes no sense (except 1:1 table relationship, but it does not substitute that need anyway) to do it on the opposite (foreign key) side.
    Even the error description is clear:
    http://ora-02270.ora-code.com/
    ORA-02270: no matching unique or primary key for this column-list
    Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
    Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •