jcon.no: Oracle blog

The other day I was asked about the following: “We have a table VARIABLE and Variable entity. A new requirement is to restrict availability of a limited number of these entities to chosen customers.” I replied: “The volumes?” And the response was: “Quite low – now around 70 rows, it might slightly exceed 100 in the future. No LOBs.”

The developer continued: “So far we have been doing such filtering by applying conditions on FK columns referencing COMPANY table. We want to do the same here. But there’s a small difference: a Variable can be either visible to all customers (no restrictions) or to 1 or more chosen customers. So, an intersection table is quite a natural choice. E.g. COMPANY_VARIABLE table with 2 columns only: FK_VARIABLE, FK_COMPANY. Only FK_VARIABLE would be not null. So, the FK referencing COMPANY table would be optional.

I’d like to avoid keeping many rows in the new table for a variable without visibility restrictions. For such variables it would be enough to have only 1 row with null in FK_COMPANY. Otherwise, it would be

• too cumbersome,
• error-prone (when adding a new company or a no-restriction variable, you’d always have to add many rows to the intersection table)
• and taking DB storage unnecessarily.

In turn, if a variable were to be restricted, we would add a new (VARIABLE_ID, COMPANY_ID) row to the table.”

My first thought was: “You can not do this, because you need a composite primary key on the two columns FK_VARIABLE and FK_COMPANY in the COMPANY_VARIABLE table. And a column which is a part of the primary key will per definition be NOT NULL”. Of course you can introduce a surrogate key and use this as a primary key, and then create a unique index on the two columns. Then you are aloud to define the FK_COMPANY as nullable.

But could I do the same thing without a surrogate key? Well I thought of one solution using an invisible virtual column. I’m not saying that this is a good solution. Then I would have to test it a little more first (Note! If you have some experience with something similar, please write a comment to this blog post).

So here is the idea:

--------------------------------------------
-- Table: CUSTOMER
--------------------------------------------
create table customer (id number);
alter table customer add constraint customer_pk primary key  (id);
insert into customer values ('1001');
insert into customer values ('1002');

--------------------------------------------
-- Table: VARIABLE
-------------------------------------------- 
create table variable (id number, variable varchar2(30));
alter table variable add constraint variable_pk primary key  (id);
insert into variable values (1, 'Test');
insert into variable values (2, 'Test 2');
insert into variable values (3, 'Test 3');

--------------------------------------------
-- Table: CUSTOMER_VARIABLE
-------------------------------------------- 
create table customer_variable (fk_customer number, fk_variable number );
alter table customer_variable add (fk_customer_all number as (case when fk_customer is null then -1 else fk_customer end ));
alter table customer_variable add (
  constraint customer_variable_pk primary key (fk_customer_all, fk_variable),
  constraint custvar_customer_fk foreign key (fk_customer) references customer (id),
  constraint custvar_variable_fk foreign key (fk_variable) references variable (id)
);
alter table customer_variable modify (fk_customer_all invisible);
 
insert into customer_variable values (null, 1);
insert into customer_variable values (null, 2);
insert into customer_variable values (1001, 1);
insert into customer_variable values (1002, 2);
insert into customer_variable values (1002, 3);
 
-- These should fail (constraint violations)
insert into customer_variable values (null, 1);
insert into customer_variable values (1002, 2);
commit;
 
-- find all valid variables for a customer
with my_variable as (
   select cv.*, rank() over (partition by fk_variable order by fk_customer nulls last) rn 
   from customer_variable cv 
   where fk_customer=:b1 or fk_customer is null
)
select fk_variable
from my_variable
where rn=1;