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
)
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
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)
)
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;
create index customer_status_lov_fk
on customers (status_code_list_id, status_id) compress
insert into customers (status_id)
values (1)
insert into customers (status_id)
values (2)
insert into customers (status_id)
values (3)
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
drop table list_values cascade constraints
drop table lists cascade constraints