iamix
asked on
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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.AGE
)
Hope this helps!
SELECT * FROM INCIDENT_DETAIL id
WHERE NOT EXISTS (
SELECT * FROM AGENCY_LIST al
WHERE id.REPORTING_AGENCY=al.AGE
)
Hope this helps!
ASKER
Mucho appreciado, pennnn!!!
You need to populate the AGENCY_IDENTIFIER column with valid REPORTING_AGENCY values from INCIDENT_DETAIL before you add the constraint...