Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4123
  • Last Modified:

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!
0
iamix
Asked:
iamix
1 Solution
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now