create table lists (
list_id number generated by default on null as identity
constraint lists_list_id_pk primary key,
list_name varchar2(100) not null
)
Table created.
create table list_values (
list_id number not null
constraint list_values_list_id_fk
references lists,
code_id number not null,
code_value varchar2(100) not null,
in_use varchar2(1) default 'Y',
constraint list_values_pk primary key (list_id, code_id),
constraint in_use_chk check (in_use in ('Y', 'N'))
)
organization index
Table created.
create table customers (
customer_id number generated by default on null as identity
constraint customers_pk primary key,
status_id number not null,
status_code_list_id invisible generated always as (1),
constraint customer_status_lov_fk foreign key (status_code_list_id, status_id)
references list_values (list_id, code_id)
)
Table created.
begin
-- customer status
insert into lists (list_id, list_name)
values (1, 'Customer status');
-- values for customer status
insert into list_values (list_id, code_id, code_value)
values (1, 1, 'Active');
insert into list_values (list_id, code_id, code_value)
values (1, 2, 'Terminated');
-- some other list
insert into lists (list_id, list_name)
values (2, 'Some other list');
-- values for some other list
insert into list_values (list_id, code_id, code_value)
values (2, 2, 'Value 1');
insert into list_values (list_id, code_id, code_value)
values (2, 3, 'Value 3');
commit;
end;
1 row(s) inserted.
create index customer_status_lov_fk
on customers (status_code_list_id, status_id) compress
Index created.
insert into customers (status_id)
values (1)
1 row(s) inserted.
insert into customers (status_id)
values (2)
1 row(s) inserted.
insert into customers (status_id)
values (3)
ORA-02291: integrity constraint (SQL_OLHDQJUFLHHEFJAULLISPHGXF.CUSTOMER_STATUS_LOV_FK) violated - parent key not found ORA-06512: at "SYS.DBMS_SQL", line 1721More Details: https://docs.oracle.com/error-help/db/ora-02291
select c.*, v.*
from customers c join list_values v
on (c.status_code_list_id = v.list_id and c.status_id = v.code_id)
CUSTOMER_ID | STATUS_ID | LIST_ID | CODE_ID | CODE_VALUE | IN_USE | 1 | 1 | 1 | 1 | Active | Y | 2 | 2 | 1 | 2 | Terminated | Y |
---|
drop table customers cascade constraints
Table dropped.
drop table list_values cascade constraints
Table dropped.
drop table lists cascade constraints
Table dropped.