ORA-02270 on trying to create a foreign key
Posted on 2006-04-03
I have an existing table, AGENCY_LIST with one column...
and there is a primary key index on this one column.
Now, I want to add a new column...
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...
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.