Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Tony.

Asked: July 26, 2004 - 8:19 am UTC

Last updated: May 23, 2008 - 6:42 pm UTC

Version: 10.1

Viewed 1000+ times

You Asked

As an advocate of enforcing as much integrity as possible declaratively, I am disappointed that little has changed in that arena in 10G. CHECK constraints are still limited to simple single-table, single-row checks, and triggers have to be used to enforce any more complex rules.

Do you think Oracle will ever add multi-table check constraints or ASSERTIONS as defined by ANSI SQL? Or has declarative integrity gone about as far as it is going to in Oracle?

and Tom said...

I'm asking around about the future of "SQL" as far as enhancments go like that (will update when I get some feedback), but -- you are not limited to triggers (in fact, I would avoid triggers as it is virtually IMPOSSIBLE to implement cross row/cross object constraints with them!!!! at least correctly)

I look to database objects to enforce them - lets look at a couple of cases (i know, these are not assertions, these are not domains -- they are working solutions using existing features in a way "perhaps not intended")

o case: we need to have a rule that enforces unique names for "current" projects. That is, we have a projects table, there are active projects and historical (completed) projects. the project name for all active projects must be unique. historical projects can have duplicates.


ops$tkyte@ORA9IR2> create table projects
2 ( id int primary key,
3 status varchar2(1) not null check (status in ('A','C')),
4 name varchar2(10)
5 )
6 /

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create unique index proj_name_idx on projects
2 ( case when status = 'A' then name end )
3 /

Index created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into projects values ( 1, 'C', 'hello' );

1 row created.

ops$tkyte@ORA9IR2> insert into projects values ( 2, 'C', 'hello' );

1 row created.

ops$tkyte@ORA9IR2> insert into projects values ( 3, 'A', 'hello' );

1 row created.

ops$tkyte@ORA9IR2> insert into projects values ( 4, 'A', 'hello' );
insert into projects values ( 4, 'A', 'hello' )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.PROJ_NAME_IDX) violated


function based indexes can be used to implemented some interesting and complex rules for conditional uniqueness

Some will say this is better solved via a data model change (eg: a projects table with subtypes "active" and "completed" -- but sometimes this is just easier to have a single physical table)

o case averages/mins/counts whatever "by something". refresh fast on commit MV's can be useful. consider, we want between 1 and 6 employees/department:

ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
Table created.

ops$tkyte@ORA9IR2> alter table emp add constraint emp_pk primary key(empno);
Table altered.

ops$tkyte@ORA9IR2> create materialized view log on emp with rowid, (deptno) including new values;
Materialized view log created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view emp_mv
2 refresh fast on commit
3 as
4 select deptno, count(*) cnt from emp group by deptno
5 /
Materialized view created.

ops$tkyte@ORA9IR2> alter table emp_mv
2 add constraint check_cnt
3 check ( cnt between 1 and 6 )
4 deferrable
5 /
Table altered.

ops$tkyte@ORA9IR2> insert into emp (empno,deptno) values ( 1000, 20 );
1 row created.

ops$tkyte@ORA9IR2> commit;
Commit complete.

ops$tkyte@ORA9IR2> insert into emp (empno,deptno) values ( 1001, 20 );
1 row created.

ops$tkyte@ORA9IR2> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (OPS$TKYTE.CHECK_CNT) violated

the logic is to "materialize" the aggregation and add a check constraint to the materialization






Can you give me an example of a multi-table assertion (real world, something you've run into). I'd like to follow up with either "won't happen" or "here is how we would do that (hopefully without triggers!)...




Rating

  (39 ratings)

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

Comments

Examples of assertions

Tony Andrews, July 27, 2004 - 9:22 am UTC

Thanks, that was very interesting and informative. Here are a couple of real world examples, which we currently have implemented by limiting table access to a package that implements the rules. I have modified them to relate to employees, departments etc. for familiarity.

Here are some sample tables:

create table project
( projno int primary key
, start_date date not null
, end_date date not null
);

create table emp_proj_assign
( empno int not null
, projno int not null
, start_date date not null
, end_date date not null
, primary key (empno, start_date)
);

And the rules:

1) An employee cannot have overlapping project assignments.

i.e. the following query must always return no rows:

select ep1.*
from emp_proj_assign ep1, emp_proj_assign ep2
where ep1.empno = ep2.empno
and ep1.start_date < ep2.start_date
and ep1.end_date >= ep2.start_date;

2) An employee's project assignment dates must fall between the project start and end dates

i.e. the following query must always return no rows:

select ep.*
from emp_proj_assign ep, project p
where ep.projno = p.projno
and ( ep.start_date < p.start_date
or ep.end_date > p.end_date);


I guess the materialized view and check constraint approach could be applied to rule 2 at least, perhaps to both?

By the way, looking at our packages that currently implement these rules, I now see that we haven't done a foolproof job: we don't lock the employee (for rule 1) or the project (for rule 2), so it would be possible for 2 concurrent sessions to violate these rules!

Tom Kyte
July 27, 2004 - 9:35 am UTC

it is even harder than you think for (1). see

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:474221407101 <code>

and ctl-f for serializable. gets really tricky in different isolation levels.

I'll agree -- overlap detection is a bear. To me it is best handled in a stored procedure that encapsulates the logic -- serializes operations on empno, and makes the check -- be it for a serializable transaction or a read committed (two different checks :(



what is wrong with Avoiding Mutating Tables with statement level triggers

Matthias Rogel, July 27, 2004 - 10:14 am UTC

I know the story of </code> http://asktom.oracle.com/~tkyte/Mutate/index.html, <code>
however, what is wrong with the following technique
(don't use row-level-trigger at all):

sample.oracle901 > create table project
2 ( projno int primary key
3 , start_date date not null
4 , end_date date not null
5 );

Tabelle wurde angelegt.

Abgelaufen: 00:00:00.02
sample.oracle901 >
sample.oracle901 > create table emp_proj_assign
2 ( empno int not null
3 , projno int not null
4 , start_date date not null
5 , end_date date not null
6 , primary key (empno, start_date)
7 );

Tabelle wurde angelegt.

Abgelaufen: 00:00:00.01
sample.oracle901 > create trigger tr_no_overl_pa
2 after insert or update on emp_proj_assign
3 declare i integer;
4 begin
5 select count(*)
6 into i
7 from emp_proj_assign ep1, emp_proj_assign ep2
8 where ep1.empno = ep2.empno
9 and ep1.start_date < ep2.start_date
10 and ep1.end_date >= ep2.start_date;
11
12 if i > 0 then
13 raise_application_error(-20001, 'An employee cannot have overlapping project assignments.');
14 end if;
15 end;
16 /

Trigger wurde erstellt.

Abgelaufen: 00:00:00.01
sample.oracle901 >
sample.oracle901 > create trigger tr_empl_as_date_b_s_and_e1
2 after insert or update on emp_proj_assign
3 declare i integer;
4 begin
5 select count(*)
6 into i
7 from emp_proj_assign ep, project p
8 where ep.projno = p.projno
9 and ( ep.start_date < p.start_date
10 or ep.end_date > p.end_date);
11 if i > 0 then
12 raise_application_error(-20001, 'An employee''s project assignment dates must fall between the project start and end dates');
13 end if;
14 end;
15 /

Trigger wurde erstellt.

Abgelaufen: 00:00:00.02
sample.oracle901 >
sample.oracle901 > create trigger tr_empl_as_date_b_s_and_e2
2 after insert or update on project
3 declare i integer;
4 begin
5 select count(*)
6 into i
7 from emp_proj_assign ep, project p
8 where ep.projno = p.projno
9 and ( ep.start_date < p.start_date
10 or ep.end_date > p.end_date);
11 if i > 0 then
12 raise_application_error(-20001, 'An employee''s project assignment dates must fall between the project start and end dates');
13 end if;
14 end;
15 /

Trigger wurde erstellt.

Abgelaufen: 00:00:00.01
sample.oracle901 >
sample.oracle901 > insert into project(projno, start_date, end_date)
2 values(1, to_date('01.01.2003','dd.mm.yyyy'), to_date('31.01.2003','dd.mm.yyyy'));

1 Zeile wurde erstellt.

Abgelaufen: 00:00:00.00
sample.oracle901 > insert into project(projno, start_date, end_date)
2 values(2, to_date('02.01.2003','dd.mm.yyyy'), to_date('28.02.2003','dd.mm.yyyy'));

1 Zeile wurde erstellt.

Abgelaufen: 00:00:00.00
sample.oracle901 >
sample.oracle901 > insert into project(projno, start_date, end_date)
2 values(3, to_date('01.02.2003','dd.mm.yyyy'), to_date('31.03.2003','dd.mm.yyyy'));

1 Zeile wurde erstellt.

Abgelaufen: 00:00:00.00
sample.oracle901 >
sample.oracle901 > insert into emp_proj_assign(empno, PROJNO, start_date, end_date)
2 values(1, 1, to_date('15.01.2003','dd.mm.yyyy'), to_date('20.01.2003','dd.mm.yyyy'));

1 Zeile wurde erstellt.

Abgelaufen: 00:00:00.01
sample.oracle901 >
sample.oracle901 > insert into emp_proj_assign(empno, PROJNO, start_date, end_date)
2 values(1, 2, to_date('22.01.2003','dd.mm.yyyy'), to_date('25.01.2003','dd.mm.yyyy'));

1 Zeile wurde erstellt.

Abgelaufen: 00:00:00.00
sample.oracle901 >
sample.oracle901 > insert into emp_proj_assign(empno, PROJNO, start_date, end_date)
2 values(1, 2, to_date('20.01.2003','dd.mm.yyyy'), to_date('26.01.2003','dd.mm.yyyy'));
insert into emp_proj_assign(empno, PROJNO, start_date, end_date)
*
FEHLER in Zeile 1:
ORA-20001: An employee cannot have overlapping project assignments.
ORA-06512: in "DP.TR_NO_OVERL_PA", Zeile 11
ORA-04088: Fehler bei der Ausführung von Trigger 'DP.TR_NO_OVERL_PA'


Abgelaufen: 00:00:00.01
sample.oracle901 >
sample.oracle901 > insert into emp_proj_assign(empno, PROJNO, start_date, end_date)
2 values(1, 3, to_date('01.04.2003','dd.mm.yyyy'), to_date('02.04.2003','dd.mm.yyyy'));
insert into emp_proj_assign(empno, PROJNO, start_date, end_date)
*
FEHLER in Zeile 1:
ORA-20001: An employee's project assignment dates must fall between the project
start and end dates
ORA-06512: in "DP.TR_EMPL_AS_DATE_B_S_AND_E1", Zeile 10
ORA-04088: Fehler bei der Ausführung von Trigger
'DP.TR_EMPL_AS_DATE_B_S_AND_E1'


Abgelaufen: 00:00:00.02
sample.oracle901 > spool off


Tom Kyte
July 27, 2004 - 11:06 am UTC

think "multi-user" and think "reads are not blocked by writes" for one thing

now does it make sense?


for another, do an update (error)
or another, do

insert into t select ....; -- don't use values, do more than one row. (error)


Multi-table constraint using MV

Tony Andrews, July 27, 2004 - 12:00 pm UTC

For my rule (2) above I have come up with this:

SQL> create materialized view log on project with rowid(projno,start_date,end_date) including new values;

Snapshot log created.

SQL> create materialized view log on emp_proj_assign with rowid(projno,start_date,end_date) including new values;

Snapshot log created.

SQL> create materialized view emp_proj_mv
  2  refresh fast on commit as
  3  select ep.projno
  4  ,      ep.start_date ep_start_date
  5  ,      ep.end_date ep_end_date
  6  ,      p.start_date p_start_date
  7  ,      p.end_date   p_end_date
  8  ,      ep.rowid ep_rowid
  9  ,      p.rowid p_rowid
 10  from emp_proj_assign ep, project p
 11  where ep.projno = p.projno
 12  /

Snapshot created.

SQL> alter table emp_proj_mv
  2  add constraint emp_proj_mv_chk1
  3  check (ep_start_date >= p_start_date and ep_end_date <= p_end_date)
  4  deferrable;

Table altered.

SQL> insert into project values (1,'01-jan-2004','31-jan-2004');

1 row created.

SQL> insert into emp_proj_assign values (1,1,'01-jan-2004','31-jan-2004');

1 row created.

SQL> commit;

Commit complete.

SQL> insert into emp_proj_assign values (1,1,'02-jan-2004','31-may-2004');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02290: check constraint (TANDREWS.EMP_PROJ_MV_CHK1) violated

Seems pretty neat.  Would it be an efficient way to do this?  I'm new to using MVs, so not sure of the pros and cons. 

Tom Kyte
July 27, 2004 - 12:57 pm UTC

there is the redundant storage,
there is the serialization on commit at the project level (to refresh the mv).

for this, I would probably stick with a more simple trigger based approach -- upon insert or update of the emp info, lock and check the project (serialization still). upon modification of project row -- lock the emp info and "cascade" the check down.


integrity paranoia

Gabe, July 27, 2004 - 2:51 pm UTC

Regarding "Declarative Integrity" ... in Tony's setup, one should of course have check constraints on PROJECT and EMP_PROJ_ASSIGN ...
"check (start_date < end_date)"

Re: integrity paranoia

Tony Andrews, July 28, 2004 - 7:18 am UTC

Of course - but I was considering rules that CAN'T be done via regular check constraints!

More on using the MV for integrity...

Tony Andrews, July 28, 2004 - 11:08 am UTC

> there is the redundant storage,

It occurred to me today that I could avoid the redundant storage by modifying the MV definition to:

create materialized view emp_proj_mv
refresh fast on commit as
select ep.projno
, ep.start_date ep_start_date
, ep.end_date ep_end_date
, p.start_date p_start_date
, p.end_date p_end_date
, ep.rowid ep_rowid
, p.rowid p_rowid
from emp_proj_assign ep, project p
where ep.projno = p.projno
and not (ep_start_date >= p_start_date and ep_end_date <= p_end_date) -- NEW CONDITION
/

The extra condition means that the MV would only contain rows that violate its own check constraint - in other words, it will always be empty!

Would you still prefer the trigger-based approach though?

Tom Kyte
July 28, 2004 - 1:20 pm UTC

neat -- that is cool, sometimes you cannot see the forest for the trees!

I'd benchmark that one.

are we still taking "Declarative Integrity" ?

Gabe, July 28, 2004 - 1:41 pm UTC

With the new MV definition there won't be an exception being raised ... one would have to do an extra select from the MV to determine if there has been a violation.

With the original MV definition the integrity is indeed declared through its check constraint and hence protected.

Dave, July 28, 2004 - 2:24 pm UTC

Tony's check constraint on the MV table would take care of raising an error.

The check constraint is still in ... missed that.

Gabe, July 28, 2004 - 2:31 pm UTC

excellent

Another common problem

Michael, July 29, 2004 - 4:03 am UTC

Hello,

wow here are really smart people!

Is there a way to use this mv-trick - no triggers - to implement the constraint, that in the classic dept-emp-schema a department must have, for example, at least 5 and at most 10 employees at the end of the transaction?

Thanks!

Tom Kyte
July 29, 2004 - 11:44 am UTC

no, the "not" and the fact we'd have to use a having clause - would prohobit the on commit attribute.

the mv would have to look like this:


create materialized view emp_dept
refresh fast on commit
as
select deptno, count(*)
from emp
group by deptno
having count(*) <5 or count(*) >10
/


the problem is -- for "deptno = 10", if it had say 10 emps, then there would be no row in the MV. Now, you insert another deptno=10 record in EMP -- there is no row in the MV to "update" -- so fast refresh (changes only) cannot work, hence "on commit" cannot work.

how to handle exceptions

Matthias Rogel, July 29, 2004 - 8:23 am UTC

nice technique with the mat. views refresh on commmit -
however how to handle exceptions ?

When the Client receives an exception like
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (DP.EMP_PROJ_MV_CHK1) violated

, ok, he can translate the name of the constraint to something
like "An employee's project assignment dates must fall between the project start and end dates ",

but imagine the following situation:
we have several integrity checks based on this technique,
and do the following

REM Setup the Case

session1 > create table project
2 ( projno int primary key
3 , start_date date not null
4 , end_date date not null
5 );

Tabelle wurde angelegt.

session1 >
session1 > create table emp_proj_assign
2 ( empno int not null
3 , projno int not null
4 , start_date date not null
5 , end_date date not null
6 , primary key (empno, start_date)
7 );

Tabelle wurde angelegt.

session1 >
session1 > create materialized view log on project with
2 rowid(projno,start_date,end_date) including new values;

Log von Materialized View wurde erstellt.

session1 >
session1 >
session1 > create materialized view log on emp_proj_assign with
2 rowid(projno,start_date,end_date) including new values;

Log von Materialized View wurde erstellt.

session1 >
session1 > create materialized view emp_proj_mv
2 refresh fast on commit as
3 select ep.projno
4 , ep.start_date ep_start_date
5 , ep.end_date ep_end_date
6 , p.start_date p_start_date
7 , p.end_date p_end_date
8 , ep.rowid ep_rowid
9 , p.rowid p_rowid
10 from emp_proj_assign ep, project p
11 where ep.projno = p.projno;

Materialized View wurde erstellt.

session1 >
session1 >
session1 >
session1 > alter table emp_proj_mv
2 add constraint emp_proj_mv_chk1
3 check (ep_start_date >= p_start_date and ep_end_date <= p_end_date)
4 deferrable;

Tabelle wurde geõndert.


REM The Case starts here


session1 >
session1 > insert into project(projno, start_date, end_date)
2 values(1, to_date('01.01.2003','dd.mm.yyyy'), to_date('31.01.2003','dd.mm.yyyy'));

1 Zeile wurde erstellt.

session1 >
session1 > insert into emp_proj_assign(empno, PROJNO, start_date, end_date)
2 values(1, 1, to_date('15.01.2003','dd.mm.yyyy'), to_date('20.01.2003','dd.mm.yyyy'));

1 Zeile wurde erstellt.

session1 >
session1 > commit;

Transaktion mit COMMIT abgeschlossen.


session2 > set transaction isolation level serializable;

Transaktion wurde gesetzt.


session1 > update project
2 set start_date = start_date+5,
3 end_date = end_date-10
4 where projno=1;

1 Zeile wurde aktualisiert.

session1 >
session1 > commit;

Transaktion mit COMMIT abgeschlossen.


session2 > update emp_proj_assign
2 set start_date = to_date('01.01.2003','dd.mm.yyyy'),
3 end_date = to_date('31.01.2003','dd.mm.yyyy')
4 where projno=1 and empno=1;

1 Zeile wurde aktualisiert.

session2 > REM do plenty of other DML on plenty of other tables
session2 > REM with integrity checks based on this technique
session2 > commit;
commit
*
FEHLER in Zeile 1:
ORA-08177: can't serialize access for this transaction



oops - Oracle doesn't tell us which tables do have problems
with their integrity.

Is there a way to find out ?



Tom Kyte
July 29, 2004 - 11:59 am UTC

umm, what does this have to do with MV's really?

this is a natural and to be expected side effect of serializable when you are accessing and modifying the same data in two sessions.



yet another question

Matthias Rogel, July 29, 2004 - 8:37 am UTC

hallo tom,

I am currenctly on
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

and planning a logical-standby-scenario

is it true that I can forget the technique described here
to implement multi-table check constraints since
deferrable constraints are not supported with logical-standby ?

Thanks

Tom Kyte
July 29, 2004 - 12:57 pm UTC

where do you see that restriction?

"mv-trick - no triggers"

A reader, July 29, 2004 - 12:28 pm UTC

> Is there a way to use this mv-trick - no triggers - to implement the constraint

Yes, if you allow the MV to actually store all the data - as Tom showed in his first reply to my original question above. i.e. without the HAVING clause.

Tom Kyte
July 29, 2004 - 1:52 pm UTC

good followup, i read it to mean "without storing the data" but yes, if you store the data.

Benchmarking the MV approach...

Tony Andrews, July 29, 2004 - 12:38 pm UTC

I am going to do some benchmarking on the various approaches, as you suggested earlier.

The approach that has the best aesthetic appeal to me at the moment (i.e. least code to write) is like this:

create materialized view emp_proj_mv2
refresh complete on commit as
select 1 dummy
from emp_proj_assign ep, project p
where ep.projno = p.projno
and (ep.start_date < p.start_date or ep.end_date > p.end_date);

alter table emp_proj_mv2
add constraint emp_proj_mv2_chk
check (1=0)
deferrable;

The COMPLETE refresh means I don't need to create materialized view logs for each table, and I don't have to select the ROWIDs of each table either. However, I would expect it to be expensive performance-wise, since it queries ALL rows in both tables at the end of each transaction. But in my informal experiments with 1000 project records and 10,000 emp_proj_assign records it hasn't been obviously slow. I will report back when I have some more scientific data.

Is it possible?

A reader, July 30, 2004 - 9:12 pm UTC

Hi Tom,
I have a lookup table, which containts lot of different columns, Is it possible in oracle to check the existance of value (while loading data) that the value in specific column must exist in lookup table through DB constraints (NOT PROGRAMMING LOGIC)?
For example: The lookup table has
Column Name Value
source_system RD ... SYS1
lookup_type STATE ... COUNTRY
lookup_code n ... U
lookup_value 01 --- U1

While loading data in STATE table like
state
description
I want to make sure that value in lookup_code must exist in lookup_code column for lookup_type = 'STATE' , source_system='RD'.
Thanks

Tom Kyte
July 31, 2004 - 11:58 am UTC

yes, if you model your lookups "properly" -- that is, in a table per lookup.

we call them foreign keys then.


looks like you are trying the old "single table for all lookups" and with that, you'll have many issues -- this data validation one being the first.

In continuation of previous question..

A reader, July 30, 2004 - 11:12 pm UTC

Tom,
Sorry, I forgot to mention that state is not PK in state table.
Thanks

Tom Kyte
July 31, 2004 - 12:02 pm UTC

it will be when you create a state lookup table!

user defined functions in mv-queries?

A reader, August 05, 2004 - 4:20 pm UTC

can one use user defined functions within the materialized view's query? an attempt to do so caused ORA-12054 although built-in functions worked well ...



Tom Kyte
August 05, 2004 - 8:43 pm UTC

[tkyte@localhost tkyte]$ oerr ora 12054
12054, 00000, "cannot set the ON COMMIT refresh attribute for the materialized view"
// *Cause:  The materialized view did not satisfy conditions for refresh at
//          commit time.
// *Action: Specify only valid options.
//


ops$tkyte@ORA9IR2> create table emp as select * from scott.emp;
 
Table created.
 
ops$tkyte@ORA9IR2> alter table emp add constraint emp_pk primary key(empno);
 
Table altered.
 
ops$tkyte@ORA9IR2> create materialized view log on emp with primary key including new values
  2  ;
 
Materialized view log created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function f( ename in varchar2 ) return varchar2
  2  deterministic
  3  as
  4  begin
  5          return lower(ename);
  6  end;
  7  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create materialized view mv
  2  refresh fast on commit
  3  as
  4  select empno, ename, f(ename) from emp;
 
Materialized view created.



give complete, YET CONCISE (eg: minimum needed to reproduce error) example.... 

More constraint

Patrick, November 10, 2004 - 6:23 pm UTC

I was wondering if it was possible to add a constraint on a table to avoid two columns to have the same data.

Here's an example:

create table tst(
colA varchar(5),
colB varchar(5)
);

insert into tst values ( 'A', 'B' ); -- it should work
insert into tst values ( 'C', 'C' ); -- it should not work cause the data are the same

What kind of constraint should I use or should I use trigger?

Thanks for your answer.

Tom Kyte
November 10, 2004 - 8:40 pm UTC

table constraint

ops$tkyte@ORA9IR2> create table tst(
  2    colA varchar(5),
  3    colB varchar(5),
  4    constraint a_not_eq_b check  ( colA <> colB )
  5  );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into tst values ( 'A', 'B' );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into tst values ( 'C', 'C' );
insert into tst values ( 'C', 'C' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.A_NOT_EQ_B) violated
 
 


note: you need to do more if you allow NULLs and want to consider NULL = NULL. 

Table, not column constraint

Patrick, November 11, 2004 - 8:46 am UTC

My error, I was trying to create the constraint on the column instead of the table.

Thanks.

Feedback?

Tony Andrews, November 25, 2004 - 1:14 pm UTC

> I'm asking around about the future of "SQL" as far as enhancments go like that (will update when I get some feedback)

Just wondering - did you get any feedback yet?

Tom Kyte
November 25, 2004 - 3:38 pm UTC

yes, but only that it is not planned for the next release -- but something that is always being looked into. SQL has gotten so huge over time....

one nice enhancement for sql

Tom, November 26, 2004 - 4:57 am UTC

Tom,

One nice enhancement for sql would be to be able to associate a sensible error message with a check constraint. ie

create table t (test date);

alter table t add constraint t_chk check (test < sysdate,'The test date must be in the past');

as one of the main reasons people give for not using check constraints is "oh we need to show the users a sensible error message". If we could define this at database level then we could simply catch all check constraints and show these error messages - no need to duplicate constraints in the front end and the db any more.

Any chance?

Tom Kyte
November 26, 2004 - 9:40 am UTC

<quote>
as one of the main reasons people give for not using check constraints
</quote>

I don't get that?  how can you show them any message at all without a check constraint?


ops$tkyte@ORA9IR2> create table t ( test number );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add constraint
  2  "This must be less than 5" check (test < 5 );
 
Table altered.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 6 );
insert into t values ( 6 )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.This must be less than 5) violated



Or

declare
   use pragma exception init to map 2290 to a named exception
begin
   insert into t ....
EXCEPTION
   when that_exception
      raise application error with any text you want.

But even more viable might be to create a mapping of "constraint name" to "custom error message" and the client application, upon getting an error message -- translates it into "end user friendly stuff"


ANY of those is 

a) infinitely superior to not having the constraints in the database
b) EASIER to implement then whatever custom code would (in vain) attemp to do it outside the database. 

Hey, I'm not disagreeing

Tom, November 26, 2004 - 11:29 am UTC

Tom,

I don't disagree that check constraints ABSOLUTELY must be done in the database, but consider the following scenario

create table projects (
id integer not null,
name varchar2(128) not null,
created_date date default sysdate,
start_date date not null,
end_date date not null,
constraint projects_pk primary key (id)
);

now lets suppose we have two constraints
1. start_date < end_date
2. start_date > created_date + 7

Obviously these are made up but gives us two constraints. What I'd like to do is....

alter table projects add constraint ordering_chk check (start_date > end_date,'end date must follow start date');
alter table projects add constraint lag_chk check (start_date > created_date+7,'start date must be at least 7 days from now');

Then my insert procedure can do
declare
exception check_violation;
pragma_exception_init(-2290,check_violation);
begin
insert into project...
exception
when check_violation then
show_error_message(sqlerrm);
end;

which would show the end use the correct message from whichever check failed.

At the moment, the only solution is to catch the error, parse out the check constraint and have a mapping table to map the name to a sensible error message.

I'm also a great believer of "if you don't have to write code....don't!" so I like the first implementation. All the functionality is implemented by Oracle so it works...

I hadn't thought of the mapping table till you mentioned it, so at the moment i do the equivalent of...

Do you still see this as useless functionality or does this explain better why I think it would be useful? Essentially, this would remove the need to have a database check [for data integrity] and an application level check [to give friendly error messages rather than "check constraint start_chk violated"].

Tom Kyte
November 26, 2004 - 12:25 pm UTC

I agree, it would be nice. Enhancement requests go through support, they come from you guys. This seems like it would be a nice one.

Will raise it

Tom, November 26, 2004 - 12:29 pm UTC

OK then, I'll raise that as an enhancement request at some point.

Until then, thanks to you for the idea about the mapping table....shouldn't be too hard to parse out the constraint name using substr etc.



Nopparat V., November 27, 2004 - 7:35 am UTC

Hi Tom

1.I have 3 tables i.e. PRODUCT_A, PRODUCT_B, PRODUCT_C. all table have a column 'ID'. I need this ID to be unique among all three tables. For example, if table Product_A have the id 595 then in product_B and Product_C should not allow this id. How can I do with any constraints? Is it possible?

2.If I have a master table Product
create table product (
id number(10) primary key,
type varchar2(1)
)
Can I have a detail table that reference the id on this product table which have type = 'A' only ?

Tom Kyte
November 27, 2004 - 9:31 am UTC

you can but it will require a data model change.

One might question "why three tables that look like they are going to basically be the same table". why three?



Seems like you are having 3 classes of products -- they probably all share some common set of attributes and then have specific attributes for their type.

Seems like a single table to me - with optional attributes that are filled in corresponding to the type. From this single table, you could even create modifiable views "product_a", "product_b" and "product_c".


Thank you for your response

Nopparat V., November 27, 2004 - 10:20 am UTC

"Seems like you are having 3 classes of products -- they probably all share some
common set of attributes and then have specific attributes for their type."

You're right. These three table share some common set of attributes. I have also thinked about a single table. But if I did that, I have to check the foreign key of detail table depend on type of the master table because product of class A could have addition detail where class B could not.

Please give me your idea.

Tom Kyte
November 27, 2004 - 10:28 am UTC

create single_table ( with all attributes );

create view product_a of single_table as select attributes for product_a only;
create view product_b of single_table as select attributes for product_b only;
create view product_c of single_table as select attributes for product_c only;


where do foreign keys come into play at all?

you might have some check constraints on single_table such as:

check ( type in ( 'A', 'B', 'C' ) ),

check (( type = 'A' and c1 is not null and c2 is not null )
or
( type = 'B' and c3 is not null and c4 is not null )
or
( type = 'C' and c5 is not null and c6 is not null )) -- not null's

check ( (type <> 'A' and c3 is null and c4 is null and c5 is null ...)
or
(type <> 'B' and c1 is null and .... )
.... ) -- make sure only A columns are filled in for type=A and so on




Sorry. My question is not clear

Nopparat V., November 27, 2004 - 10:17 pm UTC

Thanks for you answers. The detail of table A have multiple rows as it could be customized by customers. Those details should be records in another table (detail table). I don't want to use a table type as one attributes in the master table

Tom Kyte
November 28, 2004 - 9:46 am UTC

ops$tkyte@ORA9IR2> create table t ( x int, typ varchar2(1), adata int, bdata int, cdata int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t_idx on t(x,typ);
 
Index created.
 
ops$tkyte@ORA9IR2> alter table t add constraint t_unq unique(x,typ);
 
Table altered.
 
ops$tkyte@ORA9IR2> alter table t add constraint t_pk primary key(x);
 
Table altered.
 
ops$tkyte@ORA9IR2> set echo off
Wrote file /tmp/xtmpx.sql
Datatypes for Table t
 
                               Data                 Data
Column Name                    Type                 Length     Nullable
------------------------------ -------------------- ---------- --------
X                              NUMBER                          not null
TYP                            VARCHAR2             1          null
ADATA                          NUMBER                          null
BDATA                          NUMBER                          null
CDATA                          NUMBER                          null
 
 
Indexes on t
 
Index                          Is
Name                           Unique COLUMNS
------------------------------ ------ --------------------------------
T_IDX                          No     X, TYP
 
 
Triggers on t
ops$tkyte@ORA9IR2> create table dtls
  2  ( x int,
  3    typ varchar2(1) default 'A' check (typ='A'),
  4    other_data int,
  5    foreign key (x,typ) references t(x,typ)
  6  )
  7  /
 
Table created.


<b>single table that can hold a, b, c type data

dtls table can only be of typ=a however</b>

 

Thank you

Nopparat V., November 28, 2004 - 10:11 am UTC


Materialized views for lookup tables

Mike Tefft, December 03, 2004 - 11:42 am UTC

In a previous post in this thread ( 
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:21389386132607#21619604335359
) you advised against a common lookup table. 
However, I am looking at a design to use code-type-specific MVs off a common lookup table. I want to do this to allow me to have a single maintenance function for many types of lookups, while allowing RI constraints for the codes. My example:
SQL> -- Demonstration of Refresh-on-Commit Materialized View for FK constraints
SQL> --
SQL> -- Create base tables
SQL> --
SQL> CREATE TABLE CHILD_TABLE
  2      (CHILD_KEY NUMBER(3) NOT NULL,
  3       CHILD_DESC VARCHAR2(50),
  4       STATUS_CODE VARCHAR2(10),
  5       CONSTRAINT CHILD_TABLE_PK PRIMARY KEY (CHILD_KEY));

Table created.

SQL> --
SQL> CREATE TABLE CODES_TABLE
  2       (CODE_TYPE VARCHAR2(20) NOT NULL,
  3        CODE_VALUE VARCHAR2(10) NOT NULL,
  4        CODE_DESC VARCHAR2(20),
  5        CONSTRAINT CODES_TABLE_PK PRIMARY KEY (CODE_TYPE, CODE_VALUE));

Table created.

SQL> --
SQL> CREATE MATERIALIZED VIEW LOG ON CODES_TABLE
  2    WITH  ROWID, SEQUENCE( CODE_TYPE, CODE_VALUE, CODE_DESC)
  3    INCLUDING NEW VALUES;

Materialized view log created.

SQL> --
SQL> CREATE MATERIALIZED VIEW STATUS_CODES_MV
  2    BUILD IMMEDIATE
  3    REFRESH FAST ON COMMIT
  4    AS
  5    SELECT CODE_VALUE AS STATUS_CODE ,
  6            CODE_DESC  AS STATUS_DESC, count(*) as row_count
  7    FROM CODES_TABLE
  8    WHERE CODE_TYPE = 'STATUS_CODE'
  9    GROUP BY CODE_VALUE, CODE_DESC;

Materialized view created.

SQL> --
SQL> -- Add a primary key so we can define a foreign-key to it.
SQL> --
SQL> ALTER TABLE STATUS_CODES_MV
  2  ADD (CONSTRAINT STATUS_CODES_PK
  3    PRIMARY KEY (STATUS_CODE) );

Table altered.

SQL> --
SQL> -- Add a foreign-key constraint to the child table
SQL> --
SQL> ALTER TABLE CHILD_TABLE
  2          ADD  ( CONSTRAINT CHILD_STATUS_FK
  3             FOREIGN KEY (STATUS_CODE)
  4                    REFERENCES STATUS_CODES_MV) ;

Table altered.

SQL> --
SQL> -- Data setup
SQL> --
SQL> INSERT INTO CODES_TABLE (CODE_TYPE,CODE_VALUE,CODE_DESC)
  2  VALUES ('STATUS_CODE','ACTIVE','Active');

1 row created.

SQL> --
SQL> COMMIT;

Commit complete.

SQL> --
SQL> -- Now check the MV after the commit
SQL> --
SQL> SELECT STATUS_CODE, STATUS_DESC FROM STATUS_CODES_MV;

STATUS_COD STATUS_DESC                                                          
---------- --------------------                                                 
ACTIVE     Active                                                               

SQL> --
SQL> -- Insert a good child
SQL> --
SQL> INSERT INTO CHILD_TABLE (CHILD_KEY, CHILD_DESC, STATUS_CODE)
  2  VALUES (1,'Good Child','ACTIVE');

1 row created.

SQL> --
SQL> -- Insert a bad child
SQL> --
SQL> INSERT INTO CHILD_TABLE (CHILD_KEY, CHILD_DESC, STATUS_CODE)
  2  VALUES (2,'Bad Child','JUNK');
INSERT INTO CHILD_TABLE (CHILD_KEY, CHILD_DESC, STATUS_CODE)
*
ERROR at line 1:
ORA-02291: integrity constraint (XXXXX.CHILD_STATUS_FK) violated - parent key 
not found 

I am still working through some nuisance items, but I am curious about whether this is appropriate or recommended.  (The technique seems to be in the spirit of the original post.) 

Tom Kyte
December 03, 2004 - 1:23 pm UTC

Hmm, why not a VIEW with instead of triggers on it?


the instead of trigger would route the modification to the proper base table. If the modifications of the lookups were infrequent enough, I would even "not frown" on using dynamic sql and a naming convention (your CODE_TYPE attribute would be used to formulate a table name)

Thinking outside the box

Lucas Jellema, March 10, 2005 - 8:29 am UTC

This thread has been very enlightening - it provided some background to a seemingly almost between the lines remark about declarative constraint-enforcement. Thanks very much.

If Tony Andrews happens to have his performance statistics I would be most interested.

regards,

Lucas

Is it possible to create a materialized view on a union all of tables?

Mathew Butler, July 28, 2005 - 1:15 pm UTC

SQL> 
SQL> drop table a
  2  /

Table dropped

SQL> create table a ( id number, name varchar2(255))
  2  /

Table created

SQL> alter table a add constraint a_pk primary key (id)
  2  /

Table altered

SQL> create unique index a_name_uk on a(name)
  2  /

Index created

SQL> drop table b
  2  /

Table dropped

SQL> create table b ( id number, name varchar2(255))
  2  /

Table created

SQL> alter table b add constraint b_pk primary key (id)
  2  /

Table altered

SQL> create unique index b_name_uk on b(name)
  2  /

Index created

SQL> drop table c
  2  /

Table dropped

SQL> create table c ( id number, name varchar2(255))
  2  /

Table created

SQL> alter table c add constraint c_pk primary key (id)
  2  /

Table altered

SQL> create unique index c_name_uk on c(name)
  2  /

Index created

SQL> create materialized view log on a with primary key
  2  including new values
  3  /

Snapshot log created

SQL> create materialized view log on b with primary key
  2  including new values
  3  /

Snapshot log created

SQL> create materialized view log on c with primary key
  2  including new values
  3  /

Snapshot log created

SQL> drop materialized view abc_combined_uk
  2  /

drop materialized view abc_combined_uk

ORA-12003: materialized view "NPADMIN"."ABC_COMBINED_UK" does not exist

SQL> create materialized view abc_combined_uk
  2  refresh fast on commit
  3  as
  4  select a.name from a
  5  union all
  6  select b.name from b
  7  union all
  8  select c.name from c
  9  /

create materialized view abc_combined_uk
refresh fast on commit
as
select a.name from a
union all
select b.name from b
union all
select c.name from c

ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view

SQL>  

Tom Kyte
July 28, 2005 - 3:34 pm UTC

http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#574889

ops$tkyte@ORA9IR2> create materialized view log on a with rowid(name)
  2  including new values
  3  /
 
Materialized view log created.
 
ops$tkyte@ORA9IR2> create materialized view log on b with rowid(name)
  2  including new values
  3  /
 
Materialized view log created.
 
ops$tkyte@ORA9IR2> create materialized view log on c with rowid(name)
  2  including new values
  3  /
 
Materialized view log created.
 
ops$tkyte@ORA9IR2> create materialized view abc_combined_uk
  2  refresh fast on commit
  3  as
  4  select name, 1 umarker, count(*) from a group by name
  5  union all
  6  select name, 2 umarker, count(*) from b group by name
  7  union all
  8  select name, 3 umarker, count(*) from c group by name
  9  /
 
Materialized view created.
 

Thanks - brief follow on question

Mathew Butler, July 29, 2005 - 5:18 am UTC

"The SELECT list of each query must include a maintenance column, called a UNION ALL marker. The UNION ALL column must have a distinct constant numeric or string value in each UNION ALL branch. Further, the marker column must appear in the same ordinal position in the SELECT list of each query block. "

Can you explain the mechanics of the mv with respect to the marker column? I'm not clear why this marker is required?

The doco is very good at listing th erestrictions, but doesn't explain the 'why?'.

Appreciate your help.

Tom Kyte
July 29, 2005 - 8:54 am UTC

I would presume to provide "uniqueness" of the results.


Since each of A, B, and C in your case may have NAME='HELLO WORLD', it would need to know which of the three HELLO WORLDs in the MV to update when you modify table A as opposed to B.

Enforcing unique constriant on already existing table with lots of rows

A reader, November 02, 2006 - 2:17 pm UTC

Tom:

We have a big table with lots of data in it. We want to enfoce a unique constraint (existing data is duplicated) conditionally something similar to:

create unique index dept_name_unique_ix4 on dept
(case when dept_type in ('XX','YY') then dept_name end ) ;

However this index should be valid for the new data only and should not be for existing data.

Trigger is driving us crazy when we are doing it in multi user mode.

How can we enforce this conditional uniqueness for the future data and not apply for the past data?


Thanks in Advance,

Tom Kyte
November 02, 2006 - 7:35 pm UTC

trigger won't work in multi-user mode until you serialize (making it effectively SINGLE USER)


I'm not sure how this can "make sense" really - unique only for new rows?

is there some sort of date field on each record here??

are you really really sure you have thought this through???

cont/...

A reader, November 03, 2006 - 7:47 am UTC

Tom:

Yes we have data migrated from other system where there was no uniqueness enforced. Now we have to enforce the uniqueness.How do we do that?

Thanks,

Tom Kyte
November 03, 2006 - 10:43 am UTC

it does not make sense to - think about the future, someone updates an old row - what is the "rule" then? how do you tell old from new data.

Leading and or trailing spaces constraint

Bart, November 16, 2006 - 11:45 am UTC

Hello Tom,

I have a real world problem. In many databases I encounter character fields with leading or trailing spaces (even in names of customers...)

So, for my own small application, I thought of making a constraint to prevent this (clients are very bad at this - they are replaced, or they forget).
It wasn't as trivial as I thought!

In this example I use a NULLABLE field (NOT NULL fields are slightly more easier).

Oh, by adding an extra condition it was fixed - but that spoils the case.
So the question is not to fix it - but to explain the behavior.

create table test (fld varchar2(10))
/

alter table test
add constraint test_ck
check ( trim(fld) = fld
or fld is null
)
/

insert into test values (''); -- expected this to succeed...it does
insert into test values ('x'); -- expected this to succeed...it does
insert into test values (' x '); -- expected this to fail...it does
insert into test values ('x '); -- expected this to fail...it does
insert into test values (' x'); -- expected this to fail...it does

prompt From here - replace 'should' with 'I expect' ;-)
prompt here it comes...it should not insert...because (trim(fld) = fld) yields to NULL/FALSE
prompt and (fld is null) should yield to false (it does! when I isolate this condition)
insert into test values (' '); -- expected this to fail...it DOES NOT

drop table test
/

prompt *===============================================================*
prompt
prompt Variation on a theme
prompt Now the check condition is wrapped in a function
prompt *===============================================================*
prompt
create or replace function ck_trim
( p_string in varchar)
return varchar2
is
begin
if trim( p_string ) = p_string
or p_string is null
then
return 'Y';
else
return 'N';
end if;


end ck_trim;
/

Prompt
Prompt Ok means: Result outcome is as expected
Prompt Fail means: Result outcome is unexpected
Prompt No strange behavior now...at least in my view
select decode (ck_trim( ' ' ), 'N', 'Ok', 'Fail') from dual;
select decode (ck_trim( 'x ' ), 'N', 'Ok', 'Fail') from dual;
select decode (ck_trim( ' x' ), 'N', 'Ok', 'Fail') from dual;
select decode (ck_trim( 'x' ), 'Y', 'Ok', 'Fail') from dual;
select decode (ck_trim( '' ), 'Y', 'Ok', 'Fail') from dual;

drop function ck_trim;


Tom Kyte
November 16, 2006 - 3:31 pm UTC

trim( ' ' ) is null

null = value is UNKNOWN (not false)
null <> value is UNKNOWN (not false)



trim(fld) = fld and NOT (fld is not null and trim(fld) is null)

make sure trim(fld)=fld and it is not true that the fld is not null but trim(fld) is.

followup question

Bart, November 17, 2006 - 3:44 am UTC

Thank you Tom,

It's so tricky stuff.
I know I need to be alert when null's are involved.


create table test (fld varchar2(10))
/

alter table test
add constraint test_ck
check ( trim(fld) = fld )
/

insert into test values (' ');



What I didn't expect was that when a check constraint yields to UNKNOWN it is accepted as TRUE. This is contrary to conditions in PL/SQL.

I must be missing something here....




Tom Kyte
November 17, 2006 - 3:48 am UTC

it is not accepted as true at all, it is neither true, nor false, it is "unknown"



Summary so far

Bart, November 23, 2006 - 6:58 am UTC

I think I get it (I hope),

There are three possible outcomes for check constraints (well conditions in general actually - exceptions not taken into account):
TRUE     insert/update accepted
FALSE    insert/update rejected
UNKNOWN  insert/update ACCEPTED


create table test
( num  number(10)
constraint ck_num_01 check ( null = null )
)
/

Table created
insert into test( num ) values (1);

1 row inserted

SQL>  

multi row constraint

Charlie, May 22, 2008 - 4:58 pm UTC

Hi Tom,

create table t(id number(10) primary key, name varchar2(10) not null, category varchar2(1));

insert into t values(1, 'apple', 'A');
insert into t values(2, 'apple', 'A');
insert into t values(3, 'orange','B');
insert into t values(4, 'pear','C');
insert into t values(5, 'pear','C');
insert into t values(6, 'pear','C');


Is it possible to use constraints on multi rows? For example, in table "t" defined above, how do I enforce declarative integrity for -- same column "name" must have same "category", which means if you insert another ('apple', 'A') pair into the table, it would be okay. But if you insert ('apple', 'E'), it would fail because ('apple', 'A') already existed in the table.

Thanks,


Tom Kyte
May 23, 2008 - 8:12 am UTC

we call that data modeling - you have done it wrong here, you need (obviously) another schema object here.

You have de-normalized data, you did it wrong.

multi row unique constraint

Charlie, May 23, 2008 - 4:09 pm UTC

Sorry Tom, I should have made it more clear. The table T is just an example, the real table has 30 columns with different attributes. I just added two more columns to make it appear normalized.

create table t(id number(10) primary key, name varchar2(10) not null, store_id number(10) not null, category varchar2(1), sub_category number(10));

alter table t add (constraint t_uq unique (name, store_id));

insert into t values(1, 'apple', 10, 'A', 100);
insert into t values(2, 'apple', 20, 'A', 101);
insert into t values(3, 'orange', 10,'B', 100);
insert into t values(4, 'pear', 10, 'C', 100);
insert into t values(5, 'pear', 20, 'C', 101);
insert into t values(6, 'pear', 30, null, null);

commit;


Basically, what I want to achieve is that, for any given name, it only allows one category. Initially, category can be null, but once name has one category associated, it cannot take different category anymore. Is there any way to enforce this rule via declarative constraint? Triggers? Materialized View?

Can you please shed some light on it?

Thanks,
Tom Kyte
May 23, 2008 - 6:42 pm UTC

.... I just added two more columns to make it appear normalized. ...

no...

you write:

... Basically, what I want to achieve is that, for any given name, it only allows one category. ..


we are back to SQUARE ONE, you are missing an entity, category is determined by name, you are missing an entity that has name -> category. And this table of yours would have a foreign key to it.


can you explain how this would really make sense:

insert into t values(4, 'pear', 10, 'C', 100);
insert into t values(5, 'pear', 20, 'C', 101);
insert into t values(6, 'pear', 30, null, null);


? what is the real business case there.

Declarative Integrity in PostgreSQL

Peter, October 05, 2012 - 8:59 am UTC

Hi Tom,
Have you seen PostgreSQL now has a feature for declaring certain types of complex constraints (such as avoiding overlapping time periods):
http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.0#Exclusion_constraints

Perhaps Oracle could learn something from this?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library