exotic constraints

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...