We run into a scenario now and then, that we need to enforce a foreign key constraint on a column, but the referenced column d

We run into a scenario now and then, that we need to enforce a foreign key constraint on a column, but the referenced column does not have an index. This will lead to error ORA-02270. In such a case, we can use a database trigger to enforce the same constraint, as illustrated below.

sql> create table t1 ( x varchar2(10));

table created.

sql> create table lookup ( x varchar2(10));

table created.

alter table t1 add constraint t1_lookup_fk foreign key (x) references lookup(x)

error at line 1:

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

create or replace trigger "t1_lookup_fk"
before
insert or update
on t1

for each row
declare
            flag            number;
begin
            select            1
            into            flag
            from            lookup
            where            x = :new.x
            and            rownum = 1;
exception
            when no_data_found then
                        raise_application_error (-20001, 'invalid value');
            when others then
                        raise_application_error (-20020, sqlerrm);
end;