We help IT Professionals succeed at work.

ORA-02270 on trying to create a foreign key

iamix
iamix asked
on
Medium Priority
4,134 Views
Last Modified: 2008-01-09
I have an existing table, AGENCY_LIST with one column...

   AGENCY_NAME VARCHAR2(30)

and there is a primary key index on this one column.

Now, I want to add a new column...

   AGENCY_IDENTIFIER VARCHAR2(4)

which I've done through ALTER TABLE.

Then I want to create a foreign key between this new column and a column in a another table, INCIDENT_DETAIL...

   REPORTING_AGENCY VARCHAR2(4)

So, I create a unique index on the new column in AGENCY_LIST...

   CREATE UNIQUE INDEX IDX_AGENCY_IDENTIFIER ON AGENCY_LIST (AGENCY_IDENTIFIER)

which executes without error. Then I try to create the foreign key in INCIDENT_DETAIL...

   ALTER TABLE INCIDENT_DETAIL ADD (
     CONSTRAINT FK_INCIDENT_DETAIL FOREIGN KEY (REPORTING_AGENCY)
       REFERENCES AGENCY_LIST (AGENCY_IDENTIFIER))

and I get...

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

Why? My parent table (AGENCY_LIST) has a unique index on the column (AGENCY_IDENTIFIER), and the field in the child table is the same size. I will admit this is the first time I've tried to create a FK on a unique index and not a primary key.

It's been a long day. I'm sure I'm missing the forest for the trees. Someone please help.

Thanks!
Comment
Watch Question

I'm guessing that since you have nulls in AGENCY_IDENTIFIER for all rows, if's failing because it can't find the matching foreign key.

You need to populate the AGENCY_IDENTIFIER column with valid REPORTING_AGENCY values from INCIDENT_DETAIL before you add the constraint...
Commented:
You need a primary key constraint or unique CONSTRAINT on the referenced column(s) in order to create a foreign key. A unique index is not enough.
Hope that helps!

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:

you should make AGENCY_NAME in AGENCY_LIST into a primary key:

 alter table AGENCY_LIST add constraint AGENCY_LIST_pk primary key (AGENCY_NAME);

then make sure you that all the values in REPORTING_AGENCY in  INCIDENT_DETAIL  also in AGENCY_NAME in AGENCY_LIST. Otherwise, you will get error like this:

ERROR at line 1:
ORA-02298: cannot validate (xxx.your_FK_XX) - parent keys not found

Commented:
Run this query, to understand what data in table  INCIDENT_DETAIL does not exists in table AGENCY_LIST:

SELECT * FROM INCIDENT_DETAIL id
WHERE NOT EXISTS (
SELECT * FROM  AGENCY_LIST al
WHERE id.REPORTING_AGENCY=al.AGENCY_IDENTIFIER
)

Hope this helps!

Author

Commented:
Mucho appreciado,  pennnn!!!
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.