Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: Primary and foreign key..

    SQL> alter table tab1 add constraint pk1 primary key(col1,col2);

    Table altered.

    SQL> ed
    1 alter table tab2
    2 add constraint fk1 foreign key(col1)
    3 references tab1(col1)
    SQL> /
    references tab1(col1)
    *
    ERROR at line 3:
    ORA-02270: no matching unique or primary key for this column-list
    I created a primary key on col1,col2 on tab1 and Foreign key on col1 in tab2 which references col1 in tab1. But I get this error. Am I doing wrong somewhere?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    You're not matching up with the entire primary key

    Code:
    alter table tab2
    add constraint fk1 foreign key(col1, col2)
    references tab1(col1, col2);
    -cf

  3. #3
    Join Date
    Jul 2005
    Posts
    276
    SQL> alter table TAB1 drop constraint PK;

    Table altered.

    SQL> alter table TAB1 add constraint pk PRIMARY KEY(COL1,COL2);

    Table altered.

    SQL> select constraint_name,constraint_type,table_name from all_constraints
    2 where table_name='TAB1';

    CONSTRAINT_NAME C TABLE_NAME
    ------------------------------ - ------------------------------
    SYS_C00172904 C TAB1
    PK P TAB1
    SYS_C00172038 C TAB1

    SQL> ALTER TABLE TAB2 ADD CONSTRAINT FK1 FOREIGN KEY(COL1,COL2)
    2 REFERENCES TAB1(COL1,COL2);
    ALTER TABLE TAB2 ADD CONSTRAINT FK1 FOREIGN KEY(COL1,COL2)
    *
    ERROR at line 1:
    ORA-02298: cannot validate (SCHEMANAME.FK1) - parent keys not found
    TAB1 has a Primary key PK on COL1 AND COL2.
    I'm trying to create a Foreign key FK on TAB2 but it says parent keys not found, inspite of having a PK on tab1.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What the error indicates i that tab2 contains rows where the values in col1 and col2 are NOT found in tab1.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Right, 2 different error messages.
    -cf

Posting Permissions

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