Function based index as a conditional unique key

constraintoracle

I have a search table which has a generated primary key PK_ID,and also has primary keys from three or four other tables in the FK_ID column

CREATE TABLE METADATA  
(  
  PK_ID        NUMBER(10)                       NOT NULL,  
  OBJ_OWNER    VARCHAR2(35 BYTE)                NOT NULL,  
  OBJ_PACKAGE  VARCHAR2(35 BYTE),  
  OBJ_NAME     VARCHAR2(35 BYTE)                NOT NULL,  
  OBJ_TYPE     NUMBER(10)                       NOT NULL,  
  FK_ID        NUMBER(10)                       NOT NULL  
);

For example, when OBJ_TYPE = 1 then every FK_ID should be unique for that value of OBJ_TYPE.

I think I can do this with a function based index but the link to Tom Kyte's discussion on this is broken across a few web sites and I'm not sure how.
(Oracle 9.2.0.8)

A composite index will do the job. A bonus would be if a function based index could be used as a foreign key for the other tables. Is this possible?

Best Answer

I'm not completely sure that I understand what you mean by "every FK_ID should be unique for that value of OBJ_TYPE".

If you are trying to say that each FK_ID has to be unique within each OBJ_TYPE, then it sounds like you just want a composite index on (OBJ_TYPE, FK_ID).

CREATE UNIQUE INDEX idx_uniq_metadata
   ON metadata( obj_type, fk_id );

If, on the other hand, you are trying to say that if OBJ_TYPE = 1 then FK_ID must be unique but you can have duplicate FK_ID values if the OBJ_TYPE is something else, then you'd want a function-based unique index.

CREATE UNIQUE INDEX idx_uniq_metadata2
   ON metadata( (case when obj_type = 1 
                      then fk_id 
                      else null 
                   end) );

Related Solutions

Pitfalls of creating a foreign key constraint on a system table

I did not find an elegant way to solve this question but adding a check constraint on the "Owner" column of the metadata tables accomplished the objective of ensuring that only schema owners would be listed. If a new schema owner is added then all the check constraints would have to be changed but that is not too onerous.

I tip my hat to Phil for confirming that backing away slowly from system tables is the best approach and wish he had posted this as an answer.

Oracle Unique Index – Why Oracle is Not Using a Unique Index for a Long Key

(This answers the other question about why the histograms are different.)

Histograms are created by default based on column skew and whether the column was used in a relevant predicate. Copying the DDL and the data is not enough, the workload information is also important.

According to the Performance Tuning Guide:

When you drop a table, workload information used by the auto-histogram gathering feature and saved statistics history used by the RESTORE_*_STATS procedures is lost. Without this data, these features do not function properly.

For example, here is a table with skewed data but no histogram:

drop table test1;
create table test1(a date);
insert into test1 select date '2000-01-01'+level from dual connect by level <= 10;
insert into test1 select date '2000-01-01' from dual connect by level <= 1000;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
select histogram from user_tab_columns where table_name = 'TEST1';

HISTOGRAM
---------
NONE

Running the same thing, but with a query before the statistics are gathered, will generate a histogram.

drop table test1;
create table test1(a date);
insert into test1 select date '2000-01-01'+level from dual connect by level <= 10;
insert into test1 select date '2000-01-01' from dual connect by level <= 1000;
select count(*) from test1 where a = sysdate; --Only new line
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/
select histogram from user_tab_columns where table_name = 'TEST1';

HISTOGRAM
---------
FREQUENCY

Best Answer

Related Solutions

Pitfalls of creating a foreign key constraint on a system table

Oracle Unique Index – Why Oracle is Not Using a Unique Index for a Long Key

Related Question