I have an entity which does not have any natural primary key. A unique row is identified by three columns, any one of which can have a unique value and other columns will be null.
For the sake of an imaginary example, here is my entity:
--------
Product
--------
Shelf
Order
Customer
Category
--------
Only one of Shelf or Order of Customer or Category column will have a filled value, probably with an ID (will not be an FK from another table).
How do I design a table for it? I strictly need to identify unique rows solely on these columns. I am using Oracle DB if it helps.
You most probably can't.
A unique row is identified by three columns, any one of which can have a unique value and other columns will be null.
That's a showstopper. Although you CAN create unique composite key on these 4 columns, everything will be OK until you try to insert another row which already contains that combination.
For example:
SQL> create table product
2 (shelf number,
3 c_order number,
4 customer number,
5 category number,
6 --
7 constraint uk_product unique (shelf, c_order, customer, category)
8 );
Table created.
SQL> insert into product (shelf, c_order, customer, category) values (1, null, null, null);
1 row created.
SQL> insert into product (shelf, c_order, customer, category) values (2, null, 8, null);
1 row created.
SQL> insert into product (shelf, c_order, customer, category) values (1, null, 4, null);
1 row created.
SQL> insert into product (shelf, c_order, customer, category) values (1, null, null, null);
insert into product (shelf, c_order, customer, category) values (1, null, null, null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_PRODUCT) violated
This row failed because such a combination already exists (it was the very first row I inserted).
If that's OK with you - I mean, you really do expect something like this to happen - then it'll work. If not, it won't.