Today I read a post on metalink where the user wanted a unique constraint for not-null values…
Sounds easy, because Oracle never indexes null in btree index.
If I have only one column, I simply index it, it will work.
SQL> create table t66 ( n number);
Table created.
SQL> create unique index i66 on t66(n);
Index created.
SQL> insert into t66 values (null);
1 row created.
SQL> insert into t66 values (null);
1 row created.
The nulls are not indexed. In that post today, the user is using a two column index, and do not want to enforce that constraint when one of the column is null. No problem, we can use FBI to enforce this.
create index i on t(decode(col2,null,null,col1), decode(col1,null,null,col2));
so the index will contain only entries were both columns are not null.
Yesterday a user on forums.oracle.com wanted to have a not-different constraint, that is was only accepting entries [p;r] if [p;s] does not exist. It is quite hard to solved. I have read an interresting solution using ON-COMMIT-REFRESH materialized view with aggregates and constraints.
A long time ago, one user wanted a constraint “table should contain only one row”.
create unique index i on t(col*0);
would ensure at most one row, with col NOT NULL
I think I can do better.
Imagine the user wants always exactly one row in STATUS(code number)
create table STATUS_MAXONE(code number, n number default 0 primary key);
create view STATUS as select code from STATUS_MAXONE;
insert into STATUS values (null);
create table STATUS_MINONE(n number references STATUS_MAXONE(n));
insert into STATUS_MINONE values (0);
Now the user can update the view STATUS, but neither delete nor insert…
Sometimes, you can also have circular foreign key constraints, for example, a PERSON can only marry with someone who exists in PERSON, that is a PERSON.SPOUSE => PERSON.ID relation. This is also quite special… Actually, a person can only marry his spouse, that means if I am your SPOUSE, you are my SPOUSE! But there is no way to reference a column that may be null (we must reference a primary key).
Using unique index on FBI has the limitation of FBI: function must be deterministic. Same with triggers. complex constraints needs additional tables to enforce your business rules.
Like the p;r; accepting p;r; and q;s; but not p;s;
we could simply have a table containing p;s; with p as primary key…