Skip to Main Content
  • Questions
  • How to enforce conditional unique on multiple columns

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Lucy.

Asked: August 24, 2001 - 9:46 am UTC

Answered by: Tom Kyte - Last updated: November 27, 2019 - 10:49 am UTC

Category: Database - Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi, Tom,
I have a table
create table project (project_ID number primary key,
teamid number,
job varchar2(100),
status number(1));

status=1 means it is an active project, otherwise it is archived,
I was told to enforce a unique rule: the job has to be unique in the same teamid for the active projects, it means teamid and job have to be unique while status=1, what is the best way to do this?

What happens if the job can be NULL?

Thanks




and we said...

Here is one method. We can use a function based index on a function:

ops$tkyte@ORA8I.WORLD> create or replace function my_unique_function( p_teamid in number, p_job in varchar2, p_status in number ) return varchar2
2 DETERMINISTIC
3 as
4 begin
5 if ( p_status = 1 )
6 then
7 return p_teamid || '/' || p_job;
8 else
9 return NULL;
10 end if;
11 end;
12 /

Function created.

Now, we have a function that if status = 1, it will return the TEAMID || '/' || JOB (if job is null-you'll get TEAMID || '/' and thats what will be uniqued). If status is NULL or not equal to one, we return NULL.

This is nice since an index entry that is entirely NULL is not in the index (we don't put fully null entries in our b*trees), we'll only actually index the data we need to check

Now, we can create our table and index:


ops$tkyte@ORA8I.WORLD> create table project
2 (project_ID number primary key,
3 teamid number,
4 job varchar2(100),
5 status number(1)
6 );
Table created.

ops$tkyte@ORA8I.WORLD> create unique index project_idx on project( substr( my_unique_function(teamid,job,status), 1, 110 ) )
2 /

Index created.

See
</code> http://asktom.oracle.com/~tkyte/article1/index.html <code>
for why I used substr there and to discover more about function based indexes in general



ops$tkyte@ORA8I.WORLD> insert into project values( 1, 10, 'a', 1 );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into project values( 2, 10, 'a', 1 );
insert into project values( 2, 10, 'a', 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.PROJECT_IDX) violated

that shows that we cannot insert the same TEAMID/JOB with a status of 1... Lets see what happens when we update that status and try again:

ops$tkyte@ORA8I.WORLD> update project set status = 2 where project_id = 1 and teamid = 10 and status = 1;

1 row updated.

ops$tkyte@ORA8I.WORLD> insert into project values( 2, 10, 'a', 1 );

1 row created.

as expected, it works... Now, lets put some more data in there:

ops$tkyte@ORA8I.WORLD> insert into project values( 3, 10, 'a', 2 );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into project values( 4, 11, 'a', 1 );
1 row created.

ops$tkyte@ORA8I.WORLD> insert into project select rownum+50, 10, 'a', 2 from all_users where rownum <= 100;

100 rows created.


ops$tkyte@ORA8I.WORLD> select count(*) from project;

COUNT(*)
----------
104

ops$tkyte@ORA8I.WORLD> select count(*) from project where status = 1;

COUNT(*)
----------
2
So, of 104 rows in the table, 2 have a status of 1. Lets see what impact that has on our index we are using:


ops$tkyte@ORA8I.WORLD> analyze index project_idx validate structure;
Index analyzed.

ops$tkyte@ORA8I.WORLD> select lf_rows from index_stats;

LF_ROWS
----------
2

Our index has but 2 entries -- only the rows that NEED to be validated will be in our index.



Now, if you do not have the enterprise edition, you do not have function based indexes. In that case, the easiest thing for you to do would be to add another column and a trigger to maintain it plus a unique constraint.

Here is an example of that:

we will use a table called project table

ops$tkyte@ORA8I.WORLD> create table project_TABLE
2 (project_ID number primary key,
3 teamid number,
4 job varchar2(100),
5 status number(1),
6 extra number
7 );

Table created.

That extra column will hold the primary key whenever status is NOT equal to 1, that'll make it unique. It'll be NULL otherwise. We'll create a view that our applications will use -- don't want them to see this extra column:

ops$tkyte@ORA8I.WORLD> create or replace view project
2 as
3 select project_id, teamid, job, status from project_table
4 /

View created.

ops$tkyte@ORA8I.WORLD> create unique index project_idx on project_TABLE( teamid, job, extra )
2 /

Index created.

now, our unique index is on teamid, job, extra. When status = 1, extra is null so teamid, job must be unique. Whan status is not 1, extra will be the primary key and hence will be unique so that teamid/job don't have to be

ops$tkyte@ORA8I.WORLD> create or replace trigger project_trigger
2 before insert or update of status on project_TABLE
3 for each row
4 begin
5 if :new.status = 1
6 then
7 :new.extra := null;
8 else
9 :new.extra := :new.project_id;
10 end if;
11 end;
12 /

Trigger created.

that trigger does the work for us, making sure that extra is set properly. If you run the above example -- the results would be the same..





and you rated our response

  (53 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

You are great

August 24, 2001 - 1:32 pm UTC

Reviewer: A reader

Currently I am using a trigger solution, but I love the 1st method.

Thanks

Enforcing conditional uniqueness on multiple columns

August 29, 2001 - 7:46 am UTC

Reviewer: Padders from UK

Tom, this is very imaginitive use of FBI. However, would you agree that...

CREATE UNIQUE INDEX ON SUBSTR (DECODE (status, 1, teamid || '/' || job, NULL), 1, 110);

...be logically equivalent to PL/SQL function and more efficient?





Tom Kyte

Followup  

August 29, 2001 - 8:13 am UTC

Yes, it would be another way to implement the function based index. It would be slightly more efficient but since this function is only invoked on INSERT and UPDATE of that column -- I found the PLSQL function to be more "self documenting".

Either approach would work.

More on...unique condition on multiple columns

August 29, 2001 - 9:52 am UTC

Reviewer: Padders from UK

Tom - thanks for quick answer. I agree that PL/SQL function permits
more documentation. However this rather goes against your general
advice of language choice i.e. sql, plsql, java sp, c. In many cases
I suspect index naming convention would be sufficient to describe
its purpose.

I am now thinking that delimiter is a problem. If two jobs exists
with matching name except that one job has leading character which
happens to match delimiter then either version of index will not
permit combination. Perhaps it would be safer to use two column
version as in...

CREATE UNIQUE INDEX index_name ON table_name (
DECODE (status, 1, teamid),
DECODE (status, 1, job));

CREATE UNIQUE INDEX index_name ON table_name (
CASE WHEN status = 1 THEN teamid END,
CASE WHEN status = 1 THEN job END);

Tom Kyte

Followup  

August 29, 2001 - 10:10 am UTC

Agreed -- either one of these would be superior to the suggested PLSQL implementation. I would use

CREATE UNIQUE INDEX index_name ON table_name (
DECODE (status, 1, teamid, NULL ),
DECODE (status, 1, job, NULL ));

in hindsight.



More on...unique condition on multiple columns

August 29, 2001 - 10:44 am UTC

Reviewer: Padders from UK

Tom - thanks for quick answer. I agree that PL/SQL function permits
more documentation. However this rather goes against your general
advice of language choice i.e. sql, plsql, java sp, c. In many cases
I suspect index naming convention would be sufficient to describe
its purpose.

I am now thinking that delimiter is a problem. If two jobs exists
with matching name except that one job has leading character which
happens to match delimiter then either version of index will not
permit combination. Perhaps it would be safer to use two column
version as in...

CREATE UNIQUE INDEX index_name ON table_name (
DECODE (status, 1, teamid),
DECODE (status, 1, job));

CREATE UNIQUE INDEX index_name ON table_name (
CASE WHEN status = 1 THEN teamid END,
CASE WHEN status = 1 THEN job END);

Tom Kyte

Followup  

August 29, 2001 - 8:15 pm UTC

In hindsight I fully agree that the implementation of:

CREATE UNIQUE INDEX index_name ON table_name (
DECODE (status, 1, teamid, NULL ),
DECODE (status, 1, job, NULL ));

would be the best overall answer in terms of performance.



Where do I find the definition of teh function based index

August 29, 2001 - 5:34 pm UTC

Reviewer: Arup Nanda from Norwalk, CT, USA

As always, Tom, you hit a bull's eye when it comes to pin a question to ground! However, a little question to creep in here..after declaring a function based index where do I find the definition later in the dictionary? The user_ind_columns view states the name of the column for that index something like SYS_NC0005$. Where do I find the function that the index uses?

Tom Kyte

Followup  

August 29, 2001 - 8:22 pm UTC

user_ind_expression contains this info:

ops$tkyte@ORA8I.WORLD> create table project (project_ID number primary key,
  2                  teamid number,
  3                  job varchar2(100),
  4                  status number(1));

Table created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> CREATE UNIQUE INDEX project_idx ON project (
  2    DECODE (status, 1, teamid, NULL ),
  3    DECODE (status, 1, job, NULL ));

Index created.

ops$tkyte@ORA8I.WORLD> 
ops$tkyte@ORA8I.WORLD> select column_expression, column_position from user_ind_expressions
  2  /

COLUMN_EXPRESSION                        COLUMN_POSITION
---------------------------------------- ---------------
DECODE("STATUS",1,"TEAMID",NULL)                       1
DECODE("STATUS",1,"JOB",NULL)                          2 

How to enforce conditional unique on multiple columns

July 15, 2002 - 2:48 am UTC

Reviewer: Jamil Shaibani from Saudi

Hi Tom
I have try to use your funcation my_unique_function
while I am creating the unique index project_idx on project( substr(my_unique_function(teamid,job,status), 1, 110 ) )
but I am getting this message
ORA-00439 FEATURE NOT ENABLED: FUNCATION - BASED INDEXES

thanks alot
Jamil


Tom Kyte

Followup  

July 15, 2002 - 8:40 am UTC

you need Oracle8i enterprise edition for function based indexes.

Can I apply this techniques on FK?

October 29, 2002 - 4:17 pm UTC

Reviewer: Tudy from USA

Great answer!
I always want to put a conditional FK on a column.
For example, if there is one more requirement which is
" if the status is 1 the teamid should have a FK references teams.ID.
I thought I can simpley do ths following, but it did not work. Tom, can you tell me why?

ALTER TABLE project ADD CONSTRAINT FK_project_team
FOREIGN KEY (DECODE ( status, 1 , teamid )
REFERENCES team (id) ;




Tom Kyte

Followup  

October 30, 2002 - 6:50 am UTC

fkeys must be on real columns. You would need to maintain a shadow column for this to work:

create table U$YOUR_REAL_TABLE( ......, status, teamid_shadow references team, .... );

create view YOUR_REAL_TABLE
as
select ...., status, ..... /* no shadow columns */
from U$YOUR_REAL_TABLE;

create trigger t on u$your_real_table before insert or update for each row
begin
if :new.status := 1 then :new.teamid_shadow := :new.teamid;
else :new.teamid_shadow := NULL;
end if;
end;


Can function based index boost performance on big table

October 30, 2002 - 2:02 pm UTC

Reviewer: A reader

Hi Tom,
I have table which has 200 million rows . I update this table 2 times a day in batch Job .Every time I only update 2K - 3K records only .
Here is the part of the table ..
CREATE TABLE item_arc
(
end_date DATE,
parent_serial_num VARCHAR2(30),
item_serial_num VARCHAR2(30)
item_location VARCHAR2(80),
description VARCHAR2(30),
);

Here is the cursor I use to identify those 2000 - 3000
records . ..
select rowid,item_serial_num from item_arc
where end_date is null and
item_serial_num = parent_serial_num ;

Can function based index be used here and if so does it improve any performance ? .
I would really appriciate if you can provide code for the function based index here .

Thanks




Tom Kyte

Followup  

October 31, 2002 - 8:40 pm UTC

If you indexed:


create index item_arc_idx on item_arc
( decode( item_serial_num, 
              parent_serial_num, decode(end_date,
                                            NULL,1,
                                            NULL), 
              NULL ) );

and then

update item_arc
   set ....
 where decode( item_serial_num, 
              parent_serial_num, decode(end_date,
                                            NULL,1,
                                            NULL), 
              NULL ) = 1;


then it would most likely use the index (index selects 2-3k rows from the 200 million row table) and the index will only have 2-3k entries in it at any point in time since the values in the function are either 1 or NULL and entirely NULL entries are not recorded in b*tree indexes.

ops$tkyte@ORA920.US.ORACLE.COM> CREATE TABLE item_arc
  2      (
  3       end_date                       DATE,
  4       parent_serial_num              VARCHAR2(30),
  5       item_serial_num                VARCHAR2(30),
  6       item_location                  VARCHAR2(80),
  7       description                       VARCHAR2(30)
  8      );

Table created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> create index item_arc_idx on item_arc
  2  ( decode( item_serial_num,
  3                parent_serial_num, decode(end_date,
  4                                              NULL,1,
  5                                              NULL),
  6                NULL ) );

Index created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into item_arc select NULL, 'ABC', 'ABC', 'xxx', 'yyy'
  2    from all_objects
  3   where rownum <= 3000;

3000 rows created.
<b>these are indexed...</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into item_arc select NULL, 'XYZ', 'ABC', 'xxx', 'yyy'
  2    from all_objects
  3   where rownum <= 3000;

3000 rows created.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> insert into item_arc select sysdate, 'ABC', 'ABC', 'xxx', 'yyy'
  2    from all_objects
  3   where rownum <= 3000;

3000 rows created.

<b>those 6k are not</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> analyze index item_arc_idx validate structure;

Index analyzed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select name, lf_rows from index_stats;

NAME                              LF_ROWS
------------------------------ ----------
ITEM_ARC_IDX                         3000

<b>and that shows that is the case...</b>

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> analyze index item_arc_idx compute statistics;

Index analyzed.

ops$tkyte@ORA920.US.ORACLE.COM> exec dbms_stats.set_table_stats( user, 'ITEM_ARC', numrows => 200000000, numblks => 2000000 );

PL/SQL procedure successfully completed.

<b>to make it look like your environment -- index with 3k entries, table with 200,000,000 rows....</b>


ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> alter session set query_rewrite_enabled = true;

Session altered.

ops$tkyte@ORA920.US.ORACLE.COM> alter session set query_rewrite_integrity = trusted;

Session altered.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA920.US.ORACLE.COM> update item_arc
  2     set end_date = sysdate
  3   where decode( item_serial_num,
  4                parent_serial_num, decode(end_date,
  5                                              NULL,1,
  6                                              NULL),
  7                NULL ) = 1;

3000 rows updated.


Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE (Cost=19 Card=2000000 Bytes=86000000)
   1    0   UPDATE OF 'ITEM_ARC'
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'ITEM_ARC' (Cost=19 Card=2000000 Bytes=86000000)
   3    2       INDEX (RANGE SCAN) OF 'ITEM_ARC_IDX' (NON-UNIQUE) (Cost=9 Card=800000)




Statistics
----------------------------------------------------------
        109  recursive calls
       8528  db block gets
       1302  consistent gets
          0  physical reads
    1158396  redo size
        791  bytes sent via SQL*Net to client
       1020  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       3000  rows processed

ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off

ops$tkyte@ORA920.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA920.US.ORACLE.COM> analyze index item_arc_idx validate structure;

Index analyzed.

ops$tkyte@ORA920.US.ORACLE.COM> select name, lf_rows, del_lf_rows from index_stats;

NAME                              LF_ROWS DEL_LF_ROWS
------------------------------ ---------- -----------
ITEM_ARC_IDX                         3000        3000

<b>and the index is for all intents and purposes "empty" - just waiting to be filled with new data tomorrow...</b>


 

Saved my day

November 01, 2002 - 1:28 pm UTC

Reviewer: A reader

Tom,
You are the best .
Thanks

Conditional FKs

October 27, 2004 - 11:28 pm UTC

Reviewer: A reader

How would you model/implement the following: A table with a the_type (a or b or c) and the_id column. If the_type=a, the_id REFERENCES a.id, if the_type=b, the_id references b.id, if the_type=c, the_id references c.id

i.e. the FK constraint needs to be conditional based on the value in the the_type column

Are row-level triggers the only way out here?

Leaving the FK part aside for a moment, even getting information out from this type of a model would be a nightmare, no? i.e. optional (outer join) with a and b and c depending on the value of the_type?

What would be a good data model here? Thanks

Tom Kyte

Followup  

October 28, 2004 - 7:37 am UTC

you asked me how to model it -- but then give the model!


the table should be

T( x int primary key,
AID references A,
BID references B,
CID references C )


no flag, the presence of a non-null value in AID, BID, CID is more than sufficient. If only one is allowed to be NOT NULL at a time, table level check constraints can do that.


row level triggers cannot do RI without LOTS OF MANUAL locking, generally at the TABLE LEVEL!!!



October 28, 2004 - 9:33 am UTC

Reviewer: A reader

"T( x int primary key,
AID references A,
BID references B,
CID references C )"

Yes, only one of the ids should be not null, check constraint would work.

Ok now suppose I want to create a view on this that joins T and the appropriate row in either a or b or c depending on which one is not null. How would that view look?

from t,a,b,c
where t.aid=a.id(+)
and t.bid=b.id(+)
and t.cid=c.id(+)

Wouldnt this view perform horribly because of the 3 outer joins?

Thanks


Tom Kyte

Followup  

October 28, 2004 - 12:31 pm UTC

depends on my needs (and how pray tell would you do it with the other model)


I might use

select x,
decode( aid, NULL, null, (select whatever from a where id = aid ),
......
from t;


a scalar subquery.


Scalar subquery

October 28, 2004 - 12:49 pm UTC

Reviewer: A reader

select x,
decode( aid, NULL, null, (select whatever from a where id = aid ),
......
from t;

Scalar subquery wont work for me because I need many columns from A, not just 1. Any other options?

With the other model, I guess I would do a UNION ALL of 3 selects, each one exact joining with either a or b or c depending on the the_type. Hm, come to think of it, I can do that with your model as well

create view v as
select t.*,a.* from t,a where t.aid=a.id
union all
select t.*,b.* from t,b where t.bid=b.id
union all
select t.*,c.* from t,c where t.cid=c.id

So, given this view/usage requirement, what is the difference between your model and my model?

Thanks

Tom Kyte

Followup  

August 06, 2009 - 8:35 am UTC

never say "won't work for me".  there are many ways to use this.  In "Effective Oracle by Design", I showed at least three:

What if you need more than one column from the related table? Suppose we needed not only the COUNT(*), but also the AVG(OBJECT_ID). We have four choices:

0 Go back to the outer join.
0 Use two scalar subqueries.
0 Use a trick with a single scalar subquery.
0 Use an object type.

Since the first option is pretty obvious, we won't demonstrate that. We will take a look at the other choices, and demonstrate why the third and fourth options may be worthwhile. 

(4)Use Two Scalar Subqueries

First, we'll look at using two scalar subqueries:

select a.username, (select count(*)
                      from all_objects b
                     where b.owner = a.username) cnt,
                   (select avg(object_id )
                      from all_objects b
                     where b.owner = a.username) avg
  from all_users a

....

That effectively doubled the work (look at the QUERY column and compare its values to the previous results). We can get back to where we were, however, just by using a small trick.

(4)Use a Single Scalar Subquery

Instead of running two scalar subqueries, we will run one that will encode all of the data of interest in a single string. We can use SUBSTR then to pick off the fields we need and convert them to the appropriate types again.

select username,
       to_number( substr( data, 1, 10 ) ) cnt,
       to_number( substr( data, 11 ) ) avg
  from (
select a.username, (select to_char( count(*), 'fm0000000009' ) ||
                           avg(object_id)
                      from all_objects b
                     where b.owner = a.username) data
  from all_users a
       )

...

So, in the inline view, we formatted the COUNT(*) in a ten-character wide, fixed-width field. The format modifier (FM) in the TO_CHAR format suppressed the leading space that a number would have, since we know the count will never be negative (so we do not need a sign). We then just concatenate on the AVG() we want. That does not need to be fixed width, since it is the last field. I prefer to use fixed-width fields in all cases because it makes the SUBSTR activity at the next level much easier to perform. The outer query then just must SUBSTR off the fields and use TO_NUMBER or TO_DATE as appropriate to convert the strings back to their native type. As you can see, in this case, it paid off to do this extra work. 

One note of caution on this technique though: Beware of NULLs. On fields that allow NULLs, you will need to use NVL. For example, if COUNT(*) could have returned a NULL (in this case, it cannot), we would have coded this way:
nvl( to_char(count(*),'fm0000000009'), rpad( ' ', 10 ) )
That would have returned ten blanks, instead of concatenating in a NULL, which would have shifted the string over, destroying our results.

(4)Use an Object Type

Lastly, we can use an object type to return a "scalar" value that is really a complex object type. We need to start by creating a scalar type to be returned by our subquery:

ops$tkyte@ORA920> create or replace type myScalarType as object
  2  ( cnt number, average number )
  3  /

Type created.

That maps to the two numbers we would like to return: the count and the average. Now, we can get the result using this query:
select username, a.data.cnt, a.data.average
  from (
select username, (select myScalarType( count(*), avg(object_id) )
                    from all_objects b
                   where b.owner = a.username ) data
  from all_users a
       ) A
...

Here, we get the same results without needing to encode the data using TO_CHAR and decode the data using SUBSTR and TO_NUMBER. Additionally, the presence of NULLs would not further complicate the query.

Using the object type is convenient to reduce the query complexity, but it does involve the extra step of creating that type, which some people are hesitant to do. So, while this technique is easier to use, I find most people will use the encode/decode technique rather than the object type approach. The performance characteristics are very similar with either technique.

<b>for the "what is the difference between your model and my model"... I'd say

Mine is "correct" (i usually don't say that but I firmly believe it).  

Mine can use the database to enforce integrity *(which is the only way to enforce it, I'll break your trigger or application based approach in seconds unless you lock tables!).  

Mine does not encode N values into a single column.  

Mine is safer, less confusing.

And the scalar subquery will work for you.

</b>

 

Applying predicates

October 28, 2004 - 8:52 pm UTC

Reviewer: A reader

"Mine is "correct" (i usually don't say that but I firmly believe it)"

If I need to add a FK to say table D, using your model I would need to add another column (t.did references d.id). Using my model, no additional column needed, just modify the check constraint on the_type to allow 'D' and modify the FK trigger/app logic as appropriate

"Mine can use the database to enforce integrity *(which is the only way to enforce it, I'll break your trigger or application based approach in seconds unless you lock tables!)"

Here I have to agree. Using the database to enforce integrity, declaratively is the only way to go. No argument here.

Oh, all right, your model is more intuitive :)

"And the scalar subquery will work for you"

Thanks for demonstrating the various query techniques. I am a frequent reader of asktom so I already knew all these techniques.

However, as we have discussed on other threads, all of them suffer from the same shortcoming. If I apply a predicate on any of these trick columns in that view, performance would tank. It would effectively select * from v and then apply the predicate! On the other hand, with my model and the outer joins, I can at least hope to use any available indexes on those columns (with a "nested loop outer" or "hash join outer" or something). [Yes, I realize this doesnt quite apply to your demo examples with count(*) and avg(*), but I want to just select say 5 simple columns from my tables]

Tom Kyte

Followup  

October 28, 2004 - 10:09 pm UTC

<quote>
If I need to add a FK to say table D, using your model I would need to add
another column (t.did references d.id).
</quote>

Nope, wrong, zippo, zero.

to add a fk to say table d, you would need to add a fk. IF YOU DID NOT HAVE AN ATTRIBUTE THAT CAN POINT TO D, then and only then you need to (reasonably i might add) ADD A COLUMN. Have you studied anything "relational"?


SO WHAT that you need "no column" -- to me that is like the worst thing in the world. You are using "generic columns, can point to anything and acting like 'that is a good thing (tm)'". arg, ugh, nope.

<quote>
and modify the
FK trigger/app logic as appropriate
</quote>

I would LOVE to see that proposed logic, please -- begging you -- post it. I'll either

o say wow, you got it right, nice table locks, way to go or....
o do this, that and this other thing and tell me how good your application works


PLEASE -- post your "application" logic.


<quote>
If I apply a predicate on any of these trick columns in that view,
performance would tank.
</quote>

really -- GO AT IT FROM THE OTHER DIRECTION. everything thinks "single view, bammo, done"


If I have a table T that can point to A, B, C and I want all T's such that A.ATTRIBUTE > 5, me -- I would query:

select * from a, t where t.fk_to_a = a.id and a.attribute > 5;

I would NOT use a generic view from the perspective of T! that would be (what we call) wrong.


<quote>On the other hand, with my model and the outer joins, I can at least
hope to use any available indexes on those columns (with a "nested loop outer"
or "hash join outer" or something).
</quote>

that works well for somethings, scalar subqueries for others -- meaning for everytime you say "mine trumps yours", I'll have an example of mine trumping yours.

meaning -- there isn't a single solution, as always -- it depends on the question you want to ask and get an answer to.





October 28, 2004 - 10:31 pm UTC

Reviewer: A reader

"SO WHAT that you need "no column" -- to me that is like the worst thing in the world. You are using "generic columns, can point to anything and acting like 'that is a good thing (tm)'". arg, ugh, nope"

Yes, generic columns pointing to anything are bad. Reminds me of the new sys.AnyData type? :-)

Anyway, I had already agreed that your model was more intuitive and flat out "better"! I was just playing devil's advocate and finishing the line of thought I started... :)


"that works well for somethings, scalar subqueries for others -- meaning for everytime you say "mine trumps yours", I'll have an example of mine trumping yours"

Can you show me an example where scalar subquery would beat some other solution given that this is exposed in a view and a predicate might be applied on that scalar subquery column?

"meaning -- there isn't a single solution, as always -- it depends on the question you want to ask and get an answer to"

Yes, I do realize the fallacy in my way of thinking. Building stuff (views) that are too generic really ends up helping no one and hurting performance in the long run. Thanks for the sanity check.

Also, what do you think about the union all view I mentioned earlier

create view v as
select t.*,a.* from t,a where t.aid=a.id
union all
select t.*,b.* from t,b where t.bid=b.id
union all
select t.*,c.* from t,c where t.cid=c.id

This has no outer joins, would perform well if predicates are applied on the a|b|c columns, is not terribly generic, right?

Tom Kyte

Followup  

October 29, 2004 - 8:05 am UTC

<quote>
Can you show me an example where scalar subquery would beat some other solution
given that this is exposed in a view and a predicate might be applied on
that scalar subquery column?
</quote>


select * from v where t.something = value and a.somethingelse = value

(goto T and then A) -- but I was speaking "in general", for every query (any query) you have that might work better on a union all, I'll have a query that works better with a scalar subquery (not the same query, just a different one) -- and in fact, a third query that is neither UNION ALL nor scalar subquery would be best (just a query with A and T)

Your view isn't complete. when you query v, you would say (assuming A, B, C have a column VAL)

select * from v where VAL = 'XXXX';

is that a.val? b.val? c.val? -- it is all three, isn't the view "not that view"

view needs to reimplement the 'type' concept again.


The view is not 'generic' -- never was, the view would/could be used generically (used in the wrong places). The view works well for what it does.

My model your model …

October 28, 2004 - 11:58 pm UTC

Reviewer: Gabe

To "A reader" ...

I don’t see any model here (what exactly is being modeled?) … just some pattern.

<quote>How would you model/implement the following: A table with a the_type (a or b or c) and the_id column. If the_type=a, the_id REFERENCES a.id, if the_type=b, the_id references b.id, if the_type=c, the_id references c.id</quote>

<quote>Ok now suppose I want to create a view on this that joins T and the appropriate row in either a or b or c depending on which one is not null. How would that view look?</quote>

So one implements a generic child table and then wants ONE view that joins it to the relevant parent (I mean, you can quite easily create 3 views joining t with a, t with b and t with c … but apparently you want just one).

Generic table, generic view … plus other _I_wish_ or _nice_to_have_ requirements: declarative integrity and performance for every conceivable query. Sometimes something has to give … how about the _generic_ part?

I don’t know what exactly you are modeling … but I would be surprised if T wasn’t for some sort of event entity (like a “Note/Comment” … Note for an Account, Note for a Lead/Opportunity, Note for a Sale … whatever) … something that doesn’t have a natural key.

How about 3 child tables: TA(AID) references A, TB(BID) references B, TC(CID) references C? … nice mandatory FKs as well … you can also have a view of everything (should you need it … and I would be curious to know what for … probably has to do with data maintenance rather than a real business need to look across the whole thing):

create view T as
select ‘A’ the_type, aid the_id from ta
union all
select ‘B’ the_type, bid the_id from tb
union all
select ‘C’ the_type, cid the_id from tc

Solid straightforward declarative integrity, no performance worries because of outer-joins or predicated scalar queries … and if you want a bit of abstraction, don’t do it in the model, push it up a bit into your pl/sql layer/api (hope you have one).

Maybe you got it backwards … one _stack_ physical table for three distinct logical entities … why not implement three physical tables, one for each logical entity and then, maybe, add a union all view should you really needed it?


Partition view

October 29, 2004 - 10:20 am UTC

Reviewer: va from nj

"select * from v where VAL = 'XXXX';
is that a.val? b.val? c.val? -- it is all three, isn't the view "not that view"...view needs to reimplement the 'type' concept again"

You are right. What I meant to do was make it like a "partition view". Something like

create view v as
select 'A' the_type,t.*,a.* from t,a where t.aid=a.id
union all
select 'B',t.*,b.* from t,b where t.bid=b.id
union all
select 'C',t.*,c.* from t,c where t.cid=c.id

So, if user does 'select * from v where val='XXX' without specifying the_type=A|B|C, tough luck, all 3 "partitions" are scanned. The "release notes" for this view will say that specifying the_type is recommended!

Basically, it would be just a short cut to avoid creating 3 very similar views. Yes, the underlying physical data model would be solid using your suggestion, this is just a logical icing on the cake.

Thanks

Tom Kyte

Followup  

October 29, 2004 - 10:47 am UTC

and works good IF the predicate is on a,b,c -- not so good if predicate is on T

To Gabe

October 29, 2004 - 10:32 am UTC

Reviewer: va from nj

Doh, I should have read this before my earlier posting

Gabe, you hit the nail on the head! That is (in concept) exactly what this is. A event/note type of thing pointing to 3 possible parents. As to your question about business need, users are used to just specifing "val" and not the_type in their queries.

Regardless of the underlying physical model (3 child tables with mandatory FKs or one table with one of 3 mandatory FKs), if I create the UNION ALL view with 'the_type' and user does select * from v where val='xxx' would kill the partition elimination part of the union all view! As I said earlier, this is really a user training issue, cant be solved by technology?

[I think I am leaning towards 3 separate child tables and the union all view]

Thanks

October 29, 2004 - 11:10 am UTC

Reviewer: A reader

"and works good IF the predicate is on a,b,c -- not so good if predicate is on T"

Not sure I understand. If there is a predicate on the_type, then an additional predicate on a.something or t.something will be fine since that is an exact join and partition elimination will kick in.

If there is no predicate on the_type, then performance will be not so good for both a.something and t.something?

As with any union all view, the issue here is whether partition pruning kicks in or not, right? Or maybe you meant something else?

Thanks

Tom Kyte

Followup  

October 29, 2004 - 11:31 am UTC

predicate on T with UNION ALL = 3 queries on T.

predicate on T with scalar subquery = 1 query in T.

that was my point -- if you query on A, B, C and need to drive from A, B, C -- the union all is cool

if you query on T, drive from T -- then the union all view is 3 times as expensive maybe (cause you query T 3 times)

Broken link?

November 18, 2004 - 3:48 am UTC

Reviewer: Peter from UK

Tom
</code> http://asktom.oracle.com/~tkyte/article1/index.html <code>
seems to be broken... OK - I do know that others move pages on you ;-)

I think I'll need to use unique function indexes to solve this - comments (or even suggestions!)
Two column table - - A, B. The table is used to link account numbers for a loyality scheme and is similar to parent child. Rules: B is unique. A can have multiple children. No entry in B can appear in A (no grandchildren).
It is the last rule that I need to put in. Basicly this is a check for each new row that value B is not already in column A and value A is not in column B.

Tom Kyte

Followup  

November 18, 2004 - 10:38 am UTC

c/osi/asktom/g

osi = asktom, guess they removed osi from dns.

Ignore the question....

November 18, 2004 - 10:27 am UTC

Reviewer: Peter from UK

What am I thinking.... a SIMPLE before insert row trigger is all I needed....
must pay more attention!

function based composite unique index

January 06, 2005 - 1:53 pm UTC

Reviewer: A reader from New Jersey

Tom, i am having trouble creating a function based unique index - my situation is:

create table tab1
(col1 varchar2(20) not null,
col2 varchar2(20),
col3 varchar2(20));

if col2 has a value and col3 is null, unique index=(col1,col2)
if col2 has a value and col3 has a value, unique index=(col1,col2,col3)

Could you help me?

Thanks

Tom Kyte

Followup  

January 06, 2005 - 2:10 pm UTC

so, basically, when col2 is not null -- col1, col2 will be in the index and if col2 is not null AND col3 is not null -- col3 will be there as well.....


that is actually simplified to

"create a unique index on col1, col2, col3 when col2 is not null"






ops$tkyte@ORA9IR2> create table t ( c1 int not null, c2 int, c3 int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create unique index t_idx on t(
  2  case when c2 is not null then c1 end,
  3  case when c2 is not null then c2 end,
  4  case when c2 is not null then c3 end
  5  )
  6  /
 
Index created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, 1 );
insert into t values ( 1, 1, 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, null );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, 1, null );
insert into t values ( 1, 1, null )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated
 
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, null, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, null, 1 );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 1, null, null );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 1, null, null );
 
1 row created.
 
 

That works but how!!

January 06, 2005 - 4:42 pm UTC

Reviewer: A reader

Hi Tom, Thanks!! that does work. But how is the create index stmt taking care of the fact that include c3 in the index when c3 is not null. I would have thought that in the last case stmt, you would check on c3, not c2, i.e.,

case when c3 is not null then c3 end.

Thanks again.

Tom Kyte

Followup  

January 06, 2005 - 7:19 pm UTC

the nulls are not consider "unique" in an index.

in an index on c1,c2,c3 -- 1,1,NULL is the same as 1,1,NULL -- null=null *in an index*

however, if c1,c2,c3 was NULL,NULL,NULL we don't make an index entry hence entirely NULL keys are unique, but if an index key has at least one NOT NULL field....



calling parameters thru procedures

February 24, 2005 - 7:20 am UTC

Reviewer: alluri chowdary from hyderabad,india

if we r passing parameteres to procedures thru the following method i.e call proc1(emp_record.emp_deptno) then how should i have to pass parameters in the called procedure(stored procedure).

i would like to know if i have to pass parameters of the data type of the calling procedure or the parameters of the column of table i am working with.

Tom Kyte

Followup  

February 24, 2005 - 8:21 am UTC

"r"? what is that? did your keyboard accidently drop the A and E?

don't know what you are asking really though, you pass parameters to a stored procedure just like you pass parameters to any function or procedure in any language?

You should pass parameters of the correct datatype (that which the procedure is expecting) yes, to avoid implicit conversions from dates to strings or vice versa and suffering the subsequent possible loss of precision (data)

conditional referential integrity

April 07, 2005 - 5:58 am UTC

Reviewer: Sanji from India

Hi Tom !!!
We are on 10g rel 2. There is a requirement for conditional referential integrity constraints on 1 of the tables.
A column in this table has to be a foreign key to one of the primary keys of 2 different tables depending upon a criteria.
As in

CREATE TABLE TAB1
(
ID NUMBER NOT NULL,
TYPE VARCHAR2(128) ,
CONSTRAINT TYPE_FK1 FOREIGN KEY(TYPE) REFERENCES TAB2(TYPE2) DISABLE,
CONSTRAINT TYPE_FK1 FOREIGN KEY(TYPE) REFERENCES TAB3(TYPE3) DISABLE
)
/

CREATE TABLE TAB2
(
TYPE2 VARCHAR2(128),
CONSTRAINT TAB2_PK PRIMARY KEY(TYPE2) USING INDEX TABLESPACE INDX
)
/

CREATE TABLE TAB3
(
TYPE3 VARCHAR2(128),
CONSTRAINT TAB3_PK PRIMARY KEY(TYPE3) USING INDEX TABLESPACE INDX,
)
/

Depending upon TAB1.ID the foreign key constraints TYPE_FK1 & TYPE_FK1 have to enabled/ disabled.
The initial status would be DISABLE for both the constraints. For instance
if ID = 'A' then TYPE_FK1 has to enabled
if ID = 'B' the TYPE_FK2 has to enabled

I wrote a trigger for the same

CREATE OR REPLACE TRIGGER manage_constraints
BEFORE INSERT ON TAB1

DECLARE
i varchar2(128);
pragma autonomous_transaction;
-- if the pragman is not included then insert on TAB1 results in ora 4092 : cannot commit in a trigger

BEGIN

-- Before inserting any record, both the constraints should be disabled

execute immediate 'alter table TAB1 disable constraint TYPE_FK1';
execute immediate 'alter table TAB1 disable constraint TYPE_FK2';

select id into i from tab1 ;

if i = 'A' then
execute immediate 'alter table TAB1 enable constraint TYPE_FK1';
elsif i = 'B' then
execute immediate 'alter table TAB1 enable constraint TYPE_FK2';
end if;

END;
/

First time the record gets created. As apparent from
"select id into i from tab1;" for multiple records, results in

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SYS.MANAGE_CONSTRAINTS", line 7
ORA-04088: error during execution of trigger 'SYS.MANAGE_CONSTRAINTS'

So I tried creating row level trigger with "FOR EACH ROW"

IF :NEW.ID = 'A' THEN
execute immediate 'alter table TAB1 enable constraint TYPE_FK1';
ELSIF :NEW.ID = 'A' THEN
execute immediate 'alter table TAB1 enable constraint TYPE_FK2';
END IF;

This results in the following error.

ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SYS.MANAGE_CONSTRAINTS", line 8
ORA-04088: error during execution of trigger 'SYS.MANAGE_CONSTRAINTS'

Where am i going wrong and how to sort this ?

Thanks and Regards
Sanji

Tom Kyte

Followup  

April 07, 2005 - 10:14 am UTC

sorry -- but this model is backwards, or upside down.

(i view this entirely as a data model issue, not as a shortcoming in SQL or anything like that)


I don't see how enabling/disabling a constraint in a trigger is going to fix this.


the only way I see using the existing model (which I would not use personally) would be:




CREATE TABLE TAB2
(
TYPE2 VARCHAR2(128),
CONSTRAINT TAB2_PK PRIMARY KEY(TYPE2) USING INDEX TABLESPACE INDX
)
/

CREATE TABLE TAB3
(
TYPE3 VARCHAR2(128),
CONSTRAINT TAB3_PK PRIMARY KEY(TYPE3) USING INDEX TABLESPACE INDX,
)
/


CREATE TABLE TAB1_TABLE
(
ID NUMBER NOT NULL,
TYPE VARCHAR2(128) ,
type1 varchar2(128)
type2 varchar2(128)
CONSTRAINT TYPE_FK1 FOREIGN KEY(TYPE1) REFERENCES TAB2(TYPE2),
CONSTRAINT TYPE_FK1 FOREIGN KEY(TYPE2) REFERENCES TAB3(TYPE3)
)
/

create trigger t before insert or update on tab1_table
begin
if ( :new.id = 1 )
then
:new.type1 := new.type;
:new.type2 := null;
else
:new.type1 := null;
:new.type2 := new.type;
end if;
end;
/

create view tab1 as select id, type from tab1_table;



Or, I might even use an instead of insert or update on a view to not have that type column AT ALL.

In fact, it would appear that ID doesn't belong there, you don't need ID to tell if you have a type A or B if you have type1 and type2 -- the non-nullness of one or the other tells you what it is.


In response to OP titled, "Conditional FKs" by "A reader"

April 07, 2005 - 12:47 pm UTC

Reviewer: Dan Kefford from New Haven, CT

Granted, maybe this is simply prettier than using nested DECODEs or a CASE statement, but how about this?

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE a
  2         (id        VARCHAR2(1),
  3          id_desc   VARCHAR2(10),
  4* PRIMARY KEY (id))
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE b
  2         (id        VARCHAR2(1),
  3          id_desc   VARCHAR2(10),
  4* PRIMARY KEY (id))
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE c
  2         (id        VARCHAR2(1),
  3          id_desc   VARCHAR2(10),
  4* PRIMARY KEY (id))
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE t
  2         (x    NUMBER,
  3          aid  VARCHAR2(1) REFERENCES a,
  4          bid  VARCHAR2(1) REFERENCES b,
  5          cid  VARCHAR2(1) REFERENCES c,
  6* PRIMARY KEY (x))
SQL> /

Table created.

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO a
  2  SELECT 'A', 'desc a' FROM dual
  3  UNION SELECT 'B', 'desc b' FROM dual
  4* UNION SELECT 'C', 'desc c' FROM dual
SQL> /

3 rows created.

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO b
  2  SELECT 'D', 'desc d' FROM dual
  3  UNION SELECT 'E', 'desc e' FROM dual
  4* UNION SELECT 'F', 'desc f' FROM dual
SQL> /

3 rows created.

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO c
  2  SELECT 'G', 'desc g' FROM dual
  3  UNION SELECT 'H', 'desc h' FROM dual
  4* UNION SELECT 'I', 'desc i' FROM dual
SQL> /

3 rows created.

SQL> COMMIT
  2  /

Commit complete.

SQL> ed
Wrote file afiedt.buf

  1  INSERT INTO t
  2  SELECT  1,'A',NULL,NULL FROM dual
  3  UNION SELECT 2,'B',NULL,NULL FROM dual
  4  UNION SELECT 3,'C',NULL,NULL FROM dual
  5  UNION SELECT 4,NULL,'D',NULL FROM dual
  6  UNION SELECT 5,NULL,'E',NULL FROM dual
  7  UNION SELECT 6,NULL,'F',NULL FROM dual
  8  UNION SELECT 7,NULL,NULL,'G' FROM dual
  9  UNION SELECT 8,NULL,NULL,'H' FROM dual
 10* UNION SELECT 9,NULL,NULL,'I' FROM dual
SQL> /

9 rows created.

SQL> COMMIT
  2  /

Commit complete.

SQL> ed
Wrote file afiedt.buf

  1  SELECT  x,
  2          aid,
  3          bid,
  4          cid,
  5          COALESCE(t.aid, t.bid, t.cid),
  6          COALESCE(a.id_desc, b.id_desc, c.id_desc)
  7  FROM    a, b, c, t
  8  WHERE   a.id(+) = COALESCE(t.aid, t.bid, t.cid)
  9  AND     b.id(+) = COALESCE(t.aid, t.bid, t.cid)
 10* AND     c.id(+) = COALESCE(t.aid, t.bid, t.cid)
SQL> /

         X A B C C COALESCE(A
---------- - - - - ----------
         1 A     A desc a
         2 B     B desc b
         3 C     C desc c
         4   D   D desc d
         5   E   E desc e
         6   F   F desc f
         7     G G desc g
         8     H H desc h
         9     I I desc i

9 rows selected.

So... we were able to join to all three validation tables and get column values from each of them.

In terms of performance, I think it looks O.K.:

SQL> DELETE FROM plan_table
  2  /

11 rows deleted.

SQL> ed
Wrote file afiedt.buf

  1* DELETE FROM plan_table
SQL> ed
Wrote file afiedt.buf

  1  EXPLAIN PLAN FOR
  2  SELECT  /*+ FIRST_ROWS */ x,
  3          aid,
  4          bid,
  5          cid,
  6          COALESCE(t.aid, t.bid, t.cid),
  7          COALESCE(a.id_desc, b.id_desc, c.id_desc)
  8  FROM    a, b, c, t
  9  WHERE   a.id(+) = COALESCE(t.aid, t.bid, t.cid)
 10  AND     b.id(+) = COALESCE(t.aid, t.bid, t.cid)
 11* AND     c.id(+) = COALESCE(t.aid, t.bid, t.cid)
SQL> /

Explained.

SQL> set linesize 100
SQL> set pagesize 100
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation                      |  Name         | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |    16M|   701M|   842K|
|   1 |  NESTED LOOPS OUTER            |               |    16M|   701M|   842K|
|   2 |   NESTED LOOPS OUTER           |               |   800K|    28M| 42002 |
|   3 |    NESTED LOOPS OUTER          |               | 40000 |  1093K|  2002 |
|   4 |     TABLE ACCESS FULL          | T             |  2000 | 38000 |     2 |
|   5 |     TABLE ACCESS BY INDEX ROWID| A             |    20 |   180 |     1 |
|*  6 |      INDEX UNIQUE SCAN         | SYS_C0095984  |     1 |       |       |
|   7 |    TABLE ACCESS BY INDEX ROWID | B             |    20 |   180 |     1 |
|*  8 |     INDEX UNIQUE SCAN          | SYS_C0095985  |     1 |       |       |
|   9 |   TABLE ACCESS BY INDEX ROWID  | C             |    20 |   180 |     1 |
|* 10 |    INDEX UNIQUE SCAN           | SYS_C0095986  |     1 |       |       |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("A"."ID"(+)=COALESCE("T"."AID","T"."BID","T"."CID"))
   8 - access("B"."ID"(+)=COALESCE("T"."AID","T"."BID","T"."CID"))
  10 - access("C"."ID"(+)=COALESCE("T"."AID","T"."BID","T"."CID"))

Note: cpu costing is off

25 rows selected.

Note I did not add the check constraint yet (I wrote this in a hurry before a meeting!), but I think the data are valid.

I hope I properly understood the problem.
 

Can we create unique function based index as deferrable?

July 06, 2005 - 10:59 am UTC

Reviewer: A reader

Tom,

Is there anyway to create "unique function based index" as deferrable?

Thanks


Tom Kyte

Followup  

July 06, 2005 - 12:25 pm UTC

no, only constraints may be deferrable and you cannot create a constraint on a function.

no so good - prevents concurrent inserts

July 27, 2005 - 5:25 pm UTC

Reviewer: reader from PA

Tom,

I tried your technique, but you can't insert from two separate sessions with this approach.

what would be a constraint violation blocks waiting for a commit in the original session. Did you realize that?

I suspect you did, but I am not sure I like being blocked on an insert.

using this you can demonstrate the equivalent of the bitmap index deadlock on insert only transactions.


Tom Kyte

Followup  

July 27, 2005 - 6:31 pm UTC

what do you mean???

Of course you can insert from two sessions, that is precisely what this function based index is here to permit.


IF two people try to insert the SAME UNIQUE KEY, but then of course it'll block and wait (that is what happens with unique keys after all).


But I can have as many users concurrently inserting as I like here, only if they actually try to insert the *SAME KEY* would they have a problem (and that -- well, not sure what you would expect to have happen?!?!)

recall comments..

July 27, 2005 - 9:47 pm UTC

Reviewer: reader from PA

isn't there some kind of functionality on this site where you can recall your mental lapses without them being exposed for all to read. :0

I got a deadlock from the combination of the two unique keys blocking each other and was too quick to post instead of think. sorry.

good thing for anonymity around here.

Unique constraints - when a null equals another null

October 14, 2005 - 6:25 am UTC

Reviewer: Graham Oakes from London, England

Tom,

I've got a requirement for a unique constraint across two fields, the first being not nullable and the second nullable.

No problem right, after all a null is not equivalent to another null.

Well I'm getting unique constraint errors when the first field is the same and the second field is null. Does this mean two nulls are equivalent in this case?

Have you got any ideas for getting round this? The only thing I could think of was a unique function based index with and NVL on field 2 returning a dbms_random, but that doesn't guarantee uniqueness.

Here's the test case...

11:10:42 scott@GB> create table un (field1 number not null, field2 number);

Table created.

Elapsed: 00:00:00.00
11:19:53 scott@GB> alter table un add constraint un_con unique (field1, field2);

Table altered.

Elapsed: 00:00:00.00
11:20:30 scott@GB> insert into un (field1,field2) values (1,null);

1 row created.

Elapsed: 00:00:00.00
11:20:47 scott@GB> /
insert into un (field1,field2) values (1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UN_CON) violated

Thanks in advance for your help

Tom Kyte

Followup  

October 14, 2005 - 8:18 am UTC

the two nulls are equivalent when part of a concatenated key -- yes.

(null,null) would not be the same as (null,null) but
(1234,null) would be the same as (1234,null)



what is the primary key for this table?

Forget the previous post

October 14, 2005 - 6:56 am UTC

Reviewer: Graham Oakes from London, England

Tom,

please please ignore the previous post, I hadn't read through the whole thread where you give the answer I need.

Thanks anyway.
Graham

Excellent Tip

September 27, 2006 - 11:02 am UTC

Reviewer: MT from UK

Is there any fancy way of enforcing an arc'ed relationship using something similiar.

If I have a table with optional FK's to two other tables, and there is an arc, if the two columns values are entered I want an error to be generated. Only way I have currently of doing this is in a trigger (or the TAPI).


Tom Kyte

Followup  

September 27, 2006 - 3:36 pm UTC

if you mean by "arc'ed" a "subtype", then I prefer a single table

eg:

a person

is either a) a student
b) a nurse


a 1:1 relationship - I prefer a single table

person attributes, student attributes, nurse attributes


if that is not what you meant, please define "arc"

Sort of

September 28, 2006 - 2:55 am UTC

Reviewer: MT from UK

We have a distributions table with an optional fk to users and an optional fk to teams. A distribution can be for either, but not both.



Tom Kyte

Followup  

September 28, 2006 - 3:15 am UTC

then it sounds like you have two columns, both are nullable and a check constraint to ensure one or the other is null.

Yes thats ideal

September 29, 2006 - 4:19 am UTC

Reviewer: MT from UK

However didn't realise you could reference more than one column in a check constraint.

Tried this and got an ORA-02438.

ALTER TABLE distributions ADD CONSTRAINT CHECK( ( evr_kdn_code IS NOT NULL AND evr_usr_username IS NULL ) OR ( evr_kdn_code IS NULL AND evr_usr_username IS NOT NULL ) )


Do you have an example?

Ignore that

September 29, 2006 - 4:34 am UTC

Reviewer: A reader

Works fine if you name the constraint.

ALTER TABLE distributions ADD CONSTRAINT FRED CHECK( ( evr_kdn_code IS NOT NULL AND
evr_usr_username IS NULL ) OR ( evr_kdn_code IS NULL AND evr_usr_username IS NOT
NULL ) )



Tom Kyte

Followup  

September 29, 2006 - 8:32 am UTC

ops$tkyte%ORA10GR2> create table p1(x int primary key);

Table created.

ops$tkyte%ORA10GR2> create table p2(y int primary key);

Table created.

ops$tkyte%ORA10GR2> insert into p1 values (1);

1 row created.

ops$tkyte%ORA10GR2> insert into p2 values (1);

1 row created.

ops$tkyte%ORA10GR2> create table c
  2  ( x references p1,
  3    y references p2,
  4    constraint x_y_check check( nvl2(x,0,1)+nvl2(y,0,1) = 1 )
  5  )
  6  /

Table created.
<b>a short hand approach if you have lots more than two mutually exclusive things, would be deserving of a comment of course</b>

ops$tkyte%ORA10GR2> insert into c values(null,null);
insert into c values(null,null)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.X_Y_CHECK) violated


ops$tkyte%ORA10GR2> insert into c values(1,1);
insert into c values(1,1)
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.X_Y_CHECK) violated


ops$tkyte%ORA10GR2> insert into c values(1,null);

1 row created.

ops$tkyte%ORA10GR2> insert into c values(null,1);

1 row created.
 

I Like it

September 29, 2006 - 12:02 pm UTC

Reviewer: MT from UK

Thanks Tom.

FBI problem with FK constraints

October 24, 2006 - 9:07 am UTC

Reviewer: Phil from UK

Hi Tom

I'm not sure if a FBI can be used but I am trying to improve the data quality of a system that stores hierarchical info in flat tables AND in a node_tree. The data is not constrained using referential integrity so we have problems.

An example of the structure is:

NODE_TREE
==========
node_id number, -- unique id (should) references all tables below
parent_id number -- references tables below and is null for EST


ORG
===
id number,-- PK of the table
est_name varchar2(30) -- Just the name

POS
===
id number,-- PK of the table
pos_name varchar2(30) -- Just the name

What I would like to do is place deferred constraints into the tables so that if an EST row is entered and commit executed an exception is thrown. Following a data cleanse, this would mean the app or interfaces would all have to ensure that they both populate the tree and flat tables. The situation is far more complex than the above example BTW and I have been told I can not move the data out of one collection into views or mviews which would have been my choice so as to reduce the maintenance of the data in two places simultaneously.



Tom Kyte

Followup  

October 24, 2006 - 9:34 am UTC

sorry, but I don't quite know what you are trying to achieve.

what is an "est" record - and if they should never be inserted - well, you lost me there (you say "if est row is entered and commited, then fail"?)

Not sure how this tables should "relate"

EST/POS

October 24, 2006 - 10:05 am UTC

Reviewer: Phil

Est and Pos are synonymous - sorry to confuse.

Basically, it is a tree with Est(ablishment) at the top and beneath this an Org(anisation) and then a Pos(ition). I was trying to make it more straightforward and removing one level of the tree.

What I would like to do is as follows

insert into org values (1,'test org')
/

insert into node_tree (1,null)
/
commit
/

ALL OK but...
insert into org values (2,'test org')
/

commit
/
FAILURE - the node_tree is now out of synch. I hope this makes sense! Please just change est_name to pos_name and forget the est above as this should not have been referred to at all




Tom Kyte

Followup  

October 24, 2006 - 2:04 pm UTC

sorry, it does not - problem is that the problem is clear to you (you have been staring at it for weeks or more) but to us, it is very "new".

I don't know why "test org" with 2 should fail.

October 24, 2006 - 3:51 pm UTC

Reviewer: Phil

FAILURE - the node_tree is now out of synch

Table #1 The EST table...
ID's: 1,3,5

Table #2
ID's: 2,4,6

Table #3 Node Table
Ids:1,2,3,4,5,6

Testing
========
If I now insert 8 into both #2 and #3 success (synchronised)

If I insert 8 and then commit into either #1 or #2 alone, I get an error.

I think I have a solution which would use a FBI, in the FBI I declare a variable such as l_id and do a select id from (either table#1 or table#2, depending on the data) INTO l_id. This will throw an error so disallowing data with an ID in either #1 or #2 without being in the #3 node table. Hopefully this will crack it.

I probably should have stated this right at the start but I guess the goal is that I want a FK from table #3 to refer to #1 in some instances and #2 in others - these to be determined by the data itself. I think a FBI will allow this but there may well be a more 'asktom' clever option.

Thanks for your patience as ever.


Tom Kyte

Followup  

October 25, 2006 - 9:20 am UTC

sorry, we are not going to be able to answer this here, your model is ingrained in your brain - but the translation of it to text is not happening.

your FBI approach is fundementally flawed. if the function runs SQL, it is almost certainly NOT DETERMINISTIC. Hence, if you need to run a select inside the fbi, it is wrong.

There is no clever way to have a single attribute in a child table point to EITHER of tableA or tableB - there is only clever ways to implement a rational relational design to satisfy that goal.

Performance

July 21, 2007 - 7:02 am UTC

Reviewer: A reader

Tom:

We have a 17000000 row table. Every day we are going to add about 10,000 rows to this table. If I am creating a complex index to enforce uniqueness such as

create unique index tab_1_ix1 on tab1 (
case when sub_type in ('A','B') and sub_date>to_date('01-jan-2007') then sub_name
when sub_type in 'C' and sub_date>to_date('01-jan-2007','dd-mon-yyyy') and some other condition then sub_name)


Would there be a severe performance impact to enforce this uniqueness?

Thanks in Advance,
Tom Kyte

Followup  

July 21, 2007 - 10:07 am UTC

the number of rows isn't relevant here.

the complexity of the function is. You would evaluate the performance 'hit' of this based on how long that function takes to evaluate.

It would have "no performance" impact if you ask me - because it'll be the fastest way to accomplish your basic need (uniqueness given a set of rules), any other implementation would be a performance hit.

(eg: this is not any more a performance hit than "insert" is in the first place)

with 10k rows, I would not expect the entire process to take more than a few seconds.

can this be done on checkc contraints

May 25, 2009 - 4:20 am UTC

Reviewer: A reader

I was trying to implement a similar thing using constraints. if the value of col1 is "A", the col2 should be "0". else the value can be anything. i tried the following piece of code but doesnt seem to work.

db is on 10.2.0.2

create table test (col1 varchar2(10) , col2 number)
;
alter table test add constraint ck1 check (col2 decode(col1 ,'A',decode(col2,0,0,col2)))
;


Tom Kyte

Followup  

May 26, 2009 - 9:11 am UTC

ops$tkyte%ORA10GR2> create table test
  2  (col1 varchar2(10) ,
  3   col2 number,
  4   constraint check_when_a check ( case when col1 = 'A' and col2 = 0 then 1
  5                                        when col1 <> 'A' or col1 is null then 1
  6                                        else 0
  7                                    end = 1 )
  8  )
  9  ;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'A', 0 );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'A', null );
insert into test (col1, col2) values ( 'A', null )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_WHEN_A) violated


ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'A', 1 );
insert into test (col1, col2) values ( 'A', 1 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_WHEN_A) violated


ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'b', 0 );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'b', null );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( 'b', 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( null, 0 );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( null, null );

1 row created.

ops$tkyte%ORA10GR2> insert into test (col1, col2) values ( null, 1 );

1 row created.


Another scenario for FBI

May 28, 2009 - 10:19 am UTC

Reviewer: Learner from INDIA

Hi Tom
I have read your comments and they are very useful. But the scenario what I am having I am not sure whether I can use Function Based Index. Here is the scenario

CREATE TABLE event(event_ID VARCHAR2(6),brand VARCHAR2(2),status VARCHAR2(5));
ALTER TABLE event ADD (CONSTRAINT PK_event PRIMARY KEY (event_ID));
Insert into event (event_ID, brand, status) Values ('90001S', 'B1', 'OPEN');
Insert into event (event_ID, brand, status) Values ('90002S', 'B1', 'CLOSE');
Commit;
At a time only one event for a brand code can be open. Here open means the status could be ("OPEN", "ACTIVE" or "PBLSH") and the other status is "CLOSE" status. The solution mentioned above worked for me when I changed the status of event 90001S to ACTIVE it prevented me because now 90001S and 90002S are open . Is there possibility so that it also allow me to make both event status to"CLOSE".
Tom Kyte

Followup  

May 28, 2009 - 1:59 pm UTC

create unique index on event(
case when status in ('OPEN', 'ACTIVE', 'PBLSH' ) then brand end
);

uniquely index brand when status in that set.

trigger to maintain uniqueness

August 05, 2009 - 4:12 am UTC

Reviewer: Raghav from India

Hi Tom

We are using Oracle 10g. We have the below scenerio. Having before insert trigger, it fails while using Insert into .... Select From.

create table uniqtest1(
cola varchar2(20),
colb varchar2(20),
colc varchar2(20),
cold varchar2(20)
);

alter table uniqtest1 add constraint uniqtest1_pk primary key (cola);

CREATE OR REPLACE TRIGGER uniqtest1_trg
Before Insert
ON uniqtest1
Referencing New As New Old As Old
For Each Row
Declare
Lv_x Varchar2(1) :='N';
Lv_err_msg Varchar2(1000);
Lv_err Exception;

Begin
Select 'Y'
Into Lv_x
From dual
Where exists(
Select colb,colc,cold
From uniqtest1
Where colb = :New.colb
And colc = :New.colc
And cold = :New.cold
);
If lv_x = 'Y' then
raise lv_err;
End if;
Exception
When lv_err then
Lv_err_msg := 'Cannot insert duplicate value ';
raise_application_error(-20050,lv_err_msg);

When No_Data_Found Then
Lv_x := 'N';
Null;
When Others then
Lv_x := 'N';
Null;
End;
/

1. insert into uniqtest1 values ('1', 'a', 'b', 'c');

Result:
Record inserted.

2. insert into uniqtest1 values ('2', 'a', 'b', 'c');

Result:
Trigger raised the error.
ORA-20050: Cannot insert duplicate value
ORA-06512: at "RRAGHAVARAO.UNIQTEST1_TRG", line 23
ORA-04088: error during execution of trigger 'RRAGHAVARAO.UNIQTEST1_TRG'

4. If we assign the values directly in the trigger, then also the trigger was raising an error.
colb = 'a'
colc = 'b'
cold = 'c'
Result:
Trigger raised the error.
ORA-20050: Cannot insert duplicate value
ORA-06512: at "RRAGHAVARAO.UNIQTEST1_TRG", line 23
ORA-04088: error during execution of trigger 'RRAGHAVARAO.UNIQTEST1_TRG'

But...,

4. insert into uniqtest1 (cola, colb, colc, cold)
select '2',colb, colc, cold
from uniqtest1
where colb = 'a'
and colc = 'b'
and cold ='c' ;

Result:
Record Inserted.

The fourth scenerio was failing. Please let me know why this behaviour?

Thanks & Regards
Raghav
Tom Kyte

Followup  

August 05, 2009 - 9:13 am UTC

I cried as I read this

"trigger to maintain uniqueness"


I didn't even have to read the code to know that "they will do it wrong, they always do"


and I screamed in agony - seriously, IN UTTER AGONY - when I read this bit:


    When Others then
        Lv_x := 'N';
        Null;
End;


what the heck are you thinking? Seriously - this is so bad, so fundamentally bad. This shows an complete and utter sense of "we do not understand how the database works at all, not even a tiny bit"



Do you understand that you cannot see my insert and I cannot see your insert. Hence if we do something at the same time - you will absolutely get duplicates.

Do you understand that

begin
    .... your code ....
when others
   then null;
end;


basically means "if we hit any error, ignore it", you might as well ERASE ALL OF YOUR CODE BETWEEN THE BEGIN AND END. You have just, by using that when others then null, said "it is OK for this code to execute, it is OK for this code to fail, we don't really care either way and we are not going to tell ANYONE whether it worked or not - so there"




The only - REPEAT STRONGLY - the only way to do this is via:

alter table uniqtest1 add constraint uniqtest1_unq unique(colb,colc,cold);



I strongly recommend you erase from your mind the "CREATE TRIGGER" command altogether - triggers are evil, triggers are bad, triggers are to be avoided - especially if you do not understand concurrency control in the database!


Think about this, suppose your trigger could work (the error you are hiding by the way is a MUTATING TABLE constraint, we are trying really hard to protect you from yourself but you are thwarting us in that goal by the when others then null), suppose it could work.

What would happen if

in session 1 I insert into t (b,c,d) values (1,1,1);

and in session 2 You insert into t (b,c,d) values (1,1,1);

We would be both OK - My insert would fire your trigger and there would be no other 1,1,1 values. Your insert would fire your trigger and there would be no other 1,1,1 values (that you can see, my insert is NOT VISIBLE TO YOU).

Now, session 1 commits, session 2 commits - guess what you have?

A duplicate.


If you try to enforce integrity in a trigger that

a) crosses rows in a table (like this unique does)
b) crosses tables (like referential integrity)

and you have not used the LOCK TABLE T IN EXCLUSIVE MODE (serialization - one at a time)

YOU HAVE DONE IT WRONG.



and you have totally ruined my day.....


a trigger to enforce uniqueness (sad)
http://www.oracle.com/technology/oramag/oracle/08-sep/o58asktom.html
http://asktom.oracle.com/Misc/classic-example-of-why-i-despise.html

when others then null (seriously bad)
http://asktom.oracle.com/Misc/dreaded-others-then-null-strikes-again.html

trigger to maintain uniqueness

August 06, 2009 - 5:01 am UTC

Reviewer: Raghav from India

Hi Tom

Thank you for your response and sorry for troubling you. This was the case someone had done 2 years back and now the issue came up as some one tried to insert the existing value into the same table.

Thank you once again to bring us in light of avoiding triggers / avoiding trigger mutation.


Thanks & Regards
Raghav

Tom Kyte

Followup  

August 06, 2009 - 8:48 am UTC

Scary that this code has been in production for two years since

a) IT DOES NOT WORK - AT ALL


not for a single row insert with "insert values"
not for a multiple row insert with "insert select"


It never worked, never. No one tested this, no one reviewed this, no one thought about this.

This is scary - you didn't trouble me, you scare me with stuff like this.



trigger to maintain uniqueness

August 06, 2009 - 10:29 am UTC

Reviewer: Raghav from India

Hi Tom

Thank you once agian for your reply. After started viewing your site even I have started hating / avoiding triggers and trying to implement conditional unique indexes (like the one as you have suggested in country currency).

In the country currency there is a unique value 'Y' against we are checking the uniqueness and enforcing the constraint.

In the current example, we have three columns having different values on which the uniqueness has to be maintained.

Can you please let me know how to create unique index / constraint in the above scenerio.

Thanks & Regards
Raghav
Tom Kyte

Followup  

August 06, 2009 - 11:02 am UTC

I did not follow you - this is not clear:

... In the country currency there is a unique value 'Y' against we are checking the
uniqueness and enforcing the constraint.

In the current example, we have three columns having different values on which
the uniqueness has to be maintained.
....

if you mean "when country_currency='Y', these columns must be unique" then

create unique index i on t(
case when country_currency = 'Y' then colb end,
case when country_currency = 'Y' then colc end,
case when country_currency = 'Y' then cold end
);

trigger to maintain uniqueness

August 07, 2009 - 11:14 am UTC

Reviewer: Raghav from India

Hi tom

Thanks for the reply and sorry for causing confusion. I was referring to the country currency thread where there was a flag where we can filter to one record.

In the present case, there is no such column. We have only cola,colb,colc and cold
where cola is a primary key
and we need to have a uniqueness on colb,colc,cold.

Please let me know how can we achieve the uniqueness in this?

Thanks & Regards
Raghav

Tom Kyte

Followup  

August 07, 2009 - 2:38 pm UTC

alter table t add constraint t_unique_bcd unique(b,c,d)

I already said that - given the trigger code and give you saying "and we need to have a uniqueness on colb,colc,cold.", all you need is a unique constraint on b,c,d

that was in my FIRST followup:



The only - REPEAT STRONGLY - the only way to do this is via:

alter table uniqtest1 add constraint uniqtest1_unq unique(colb,colc,cold);

FBI to enforce uniqueness

August 27, 2009 - 11:41 pm UTC

Reviewer: Ian from Australia

Hi Tom, awesome site

hope you think this is relevant to the post - i'm having trouble enforcing uniqueness on a character column using a simple function based index, can you please shine some light...

the following parameters and privs have been set as per docs:

SQL> select name, value from v$parameter
  2  where upper(name) in ('QUERY_REWRITE_INTEGRITY','QUERY_REWRITE_ENABLED','COMPATIBLE');

NAME                                                                             VALUE
-------------------------------------------------------------------------------- -------------------
compatible                                                                       10.2.0.4
query_rewrite_enabled                                                            TRUE
query_rewrite_integrity                                                          TRUSTED

SQL> 
SQL> 
SQL> select * from dba_sys_privs
  2  where grantee = user
  3  and privilege in ('GLOBAL QUERY REWRITE', 'QUERY REWRITE')
  4  /

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
EDENTIFY                       QUERY REWRITE                            NO
EDENTIFY                       GLOBAL QUERY REWRITE                     NO


simple table to test duplicate inserts:

create table contacts 
  (dacr_id NUMBER(10)    PRIMARY KEY NOT NULL
  ,email   VARCHAR2(100)             NOT NULL);

CREATE UNIQUE INDEX contacts_uk
ON contacts upper(email);

ANALYZE TABLE contacts COMPUTE STATISTICS;

insert into contacts values (dacr_seq.nextval, 'dave@hello.com');
insert into contacts values (dacr_seq.nextval, 'DAVE@hello.com');



SQL> create table contacts 
  2    (dacr_id NUMBER(10)    PRIMARY KEY NOT NULL
  3    ,email   VARCHAR2(100)             NOT NULL);

Table created.

SQL> 
SQL> CREATE UNIQUE INDEX contacts_uk
  2  ON contacts upper(email);

Index created.

SQL> 
SQL> ANALYZE TABLE contacts COMPUTE STATISTICS;

Table analyzed.

SQL> 
SQL> insert into contacts values (100, 'dave@hello.com');

1 row created.

SQL> insert into contacts values (101, 'DAVE@hello.com');

1 row created.




My question: Why does the 2nd insert not fail with a unique constraint error? what am i missing here??

Thanks for your time!

Tom Kyte

Followup  

August 28, 2009 - 5:18 pm UTC

hah, funny

ops$tkyte%ORA10GR2> CREATE UNIQUE INDEX contacts_uk ON contacts i_am_a_correlation_name(email);

Index created.


well, it amused me anyway - upper, in your application of it, was not a function it was a correlation name.


ops$tkyte%ORA10GR2> CREATE UNIQUE INDEX contacts_uk ON contacts (upper(email));

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> ANALYZE TABLE contacts COMPUTE STATISTICS;

Table analyzed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into contacts values (dacr_seq.nextval, 'dave@hello.com');

1 row created.

ops$tkyte%ORA10GR2> insert into contacts values (dacr_seq.nextval, 'DAVE@hello.com');
insert into contacts values (dacr_seq.nextval, 'DAVE@hello.com')
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.CONTACTS_UK) violated

uniqye index

December 23, 2009 - 11:16 am UTC

Reviewer: A reader


October 18, 2017 - 9:40 pm UTC

Reviewer: Alex

Hi folks,

I have a dev team trying to come up with a way to prevent a circular reference to a table with a trigger. I was hoping there is a better way like a check constraint because I have transactional + performance concerns. But there's a lot of logic for that so I'm wondering what your ideas are, here's the code:

CREATE TABLE role_privs_copy AS
SELECT * FROM role_privs;

CREATE OR REPLACE TRIGGER circular_reference BEFORE
  INSERT OR
  UPDATE OR
  DELETE ON role_privs FOR EACH ROW DECLARE flag INTEGER;
  BEGIN
    IF inserting THEN
      INSERT
      INTO role_privs_copy VALUES
        (
          :NEW.role_id,
          :NEW.permission,
          :NEW.granted_role_id
        );
    END IF;
    IF updating THEN
      UPDATE role_privs_copy
      SET granted_role_id      =:NEW.granted_role_id,
        role_id                =:NEW.role_id,
        permission             =:NEW.permission
      WHERE (role_id           =:OLD.role_id
      OR :OLD.role_id         IS NULL)
      AND (granted_role_id     =:OLD.granted_role_id
      OR :OLD.granted_role_id IS NULL)
      AND (permission          =:OLD.permission
      OR :OLD.permission      IS NULL);
    END IF;
    IF deleting THEN
      DELETE
      FROM role_privs_copy
      WHERE role_id      =:OLD.role_id
      AND granted_role_id=:OLD.granted_role_id;
    END IF;
    SELECT count(*)
    INTO flag
    FROM role_privs_copy
    WHERE role_id                      = :NEW.granted_role_id
      START WITH granted_role_id       = :NEW.role_id
      CONNECT BY NOCYCLE PRIOR role_id = granted_role_id;
    IF (flag                           > 0) THEN
      raise_application_error(-20001, 'Granting role ' || :NEW.granted_role_id || ' to role ' || :NEW.role_id || ' would result in a circular reference');
    END IF;
  END;
  /


Thanks.
Connor McDonald

Followup  

October 19, 2017 - 2:16 am UTC

Yeah, check constraint is out for this one, you definitely need a trigger.

You'll need a compound trigger to defer the work until after-statement time.

Example here:

https://asktom.oracle.com/pls/apex/asktom.search?tag=compound-triggers


contents from Tom's blog

July 26, 2018 - 1:06 pm UTC

Reviewer: Rajeshwaran Jeyabal

Team,

this page has references to this link

https://asktom.oracle.com/Misc/dreaded-others-then-null-strikes-again.html

but that seem to be a dead link, could you help us get it loaded?
Connor McDonald

Followup  

July 27, 2018 - 1:35 am UTC

that should be there in the next couple of days

contents from Tom's blog

August 13, 2018 - 1:23 am UTC

Reviewer: Rajeshwaran, Jeyabal

Team,

the above link is not yet available, could you please help.
Connor McDonald

Followup  

August 15, 2018 - 11:51 pm UTC

Hmmm...will check with our site support people. It should have been done ages ago

contents from Tom's blog

August 17, 2018 - 7:38 am UTC

Reviewer: Rajeshwaran, Jeyabal

Many thanks. That link is working now !

November 26, 2019 - 1:22 pm UTC

Reviewer: Jess from USA

Dear Oracle Masters,

If you have a table with columns A and B (no nulls in either), is it possible to have one index such that A is always unique regardless of value of B (i.e., it's a unique index on A that stores the value of B along for the ride to improve some queries that select B based on value of A)?

(Obviously one can have a unique index on A and a non-unique index on (A, B), but is there a way to do this with one index)?

Many thanks as always!

Chris Saxon

Followup  

November 26, 2019 - 4:09 pm UTC

You can create a unique constraint over the leading columns of a non-unique index.

This enforces uniqueness on a subset of columns. But it's still only one index. Which the database can use for index-only scans of all the columns:

create table t (
  c1 int, c2 int,
  unique ( c1 ) 
    using index (
      create index i 
        on t ( c1, c2 )
    )
);

insert into t values ( 1, 1 );
insert into t values ( 1, 2 );

ORA-00001: unique constraint (CHRIS.SYS_C0014846) violated

insert into t values ( 2, 1 );
insert into t values ( 2, 2 );

ORA-00001: unique constraint (CHRIS.SYS_C0014846) violated

set serveroutput off

select c2 from t
where  c1 = 1;

C2   
    1 

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

---------------------------------    
| Id  | Operation        | Name |    
---------------------------------    
|   0 | SELECT STATEMENT |      |    
|   1 |  INDEX RANGE SCAN| I    |    
---------------------------------

November 26, 2019 - 7:01 pm UTC

Reviewer: Jess from USA

Thank you Chris, that is a really clever way of doing it!!!
Chris Saxon

Followup  

November 27, 2019 - 10:49 am UTC

You're welcome :)