Mohamed Houri’s Oracle Notes

October 8, 2013

On how to enforce uniqueness when unique constraint is not possible

Filed under: Oracle — hourim @ 3:21 pm

The following select gives a list of existing table that do not possess a unique constraint

select table_name
from user_tables u1
where not exists (select null
from  user_constraints u2
where u2.table_name      = u1.table_name
and   u2.constraint_type = 'U');

However, the above select doesn’t mean that there isn’t any other uniqueness enforcement  implemented without a dedicated unique constraint.

Recently, I have been confronted to a business rule to be implemented in a Data Vault model which specifies that each Satellite or Satellite of Link table should have a unique key enforcement satisfying the following rule

For each couple of (col1, col2) I could have at most a unique record for a not null dv_load_end_date column.

A picture being worth a thousand words let’s see this through a real example


SQL> create table t1
(t1_sk            number
,dv_load_date     date
,seq_nbr          number
,n1               number
,n2               number
,status           varchar2(10)
,identification   varchar2(30)
,dv_load_end_date date);

SQL> alter table t1 add constraint t1_pk primary key (t1_sk,dv_load_date, seq_nbr);

The unique business rule would be described as follows: for each couple (t1_sk, n2) I could have at most one record for a null dv_load_end_date column

Is this doable via a unique constraint? No it is not.

And here where function based index comes to the rescue

SQL> create unique index ind_t1_uk on t1
(case when dv_load_end_date is null then t1_sk else null end
,case when dv_load_end_date is null then n2 else null end);

And here how the inserts go

SQL> insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
              values(1, sysdate, 1, 1, 100, null);

1 row created.

SQL> insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
              values(1, sysdate, 1, 1, 100, sysdate);

1 row created.

SQL> insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
              values(1, sysdate, 1, 1, 100, null);

insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
*
ERROR at line 1:
ORA-00001: unique constraint (XXX.IND_T1_UK) violated

SQL> insert into t1 (t1_sk ,dv_load_date ,seq_nbr,n1,n2,dv_load_end_date)
              values(1, sysdate, 1, 1, 200, null);
1 row created.

This post is the second one in a set of small scripts (blog articles) I decided to collect in my blog for my documentation and reuse purposes

2 Comments »

  1. Mohamed, in your table t1, column n1 is the primary key. Therefore you can only ever have record containing one value for this column throughout the entire table – irrespective of any other column values!

    For your idea to have any merit, you must make a different column the primary key.

    Comment by Martin Rose — October 8, 2013 @ 3:46 pm | Reply

  2. Martin,

    You are absolutely right.

    Why haven’t I taken my real life example and modeled it as it is? This is what I have finally done and updated the blog

    Thanks

    Comment by hourim — October 9, 2013 @ 7:01 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: