ORA-02270 on trying to create a foreign key

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!
iamixAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DeanHorakCommented:
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...
0
pennnnCommented:
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!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geotigerCommented:

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

0
radja7Commented:
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!
0
iamixAuthor Commented:
Mucho appreciado,  pennnn!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.