Home » SQL & PL/SQL » SQL & PL/SQL » Check Constraint (Oracle 10g Release 10.2.0.1.0 , UNIX)
Check Constraint [message #413296] Wed, 15 July 2009 03:06 Go to next message
johnbach
Messages: 32
Registered: June 2009
Member
create table txn (type number,id number);


type can have value 100,400

The id should be unique for type 100
and should allow duplicate for type 400.

Example:
  TYPE  ID
   100   1
   100   2
   100   3
   400   1
   400   1
   400   5
   400   5



Could any one help me with a check constraint ?
Re: Check Constraint [message #413297 is a reply to message #413296] Wed, 15 July 2009 03:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I can't think of a way of doing it with just a check constraint, but you can do it with a check constraint and a unique index:
create table test_012 (col_1  number);

alter table test_012 add (constraint test_012_ck check (col_1 in (100,400)));

create unique index test_012_uq_idx on test_012(case when col_1 =400 then null else col_1 end);

insert into test_012 values (100);
insert into test_012 values (100);
insert into test_012 values (400);
insert into test_012 values (400);
insert into test_012 values (400);
insert into test_012 values (400);
Re: Check Constraint [message #413300 is a reply to message #413296] Wed, 15 July 2009 03:34 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
The first thing I would tell you is to create two different tables for two (surely!!) different entities. keep in mind that in a relational database the use of different tables for different things is probably the most important rule to follow.

But if you can't really avoid to change your data-model you can do it with an on commit fast refreshable supplementary MV and a unique constraint on it, like the in the following case.

SQL> create table txn (
  2     type number,
  3     id number
  4  )
  5  /

Table created.

SQL>
SQL> create materialized view log on txn with rowid
  2  /

Materialized view log created.

SQL>
SQL> create materialized view txn_mv
  2  refresh fast on commit
  3  with rowid
  4  as
  5  select id
  6  from txn
  7  where type = 100
  8  /

Materialized view created.

SQL>
SQL> alter table txn_mv add (
  2     constraint txn_mv_unq
  3             unique(id)
  4  )
  5  /

Table altered.

SQL>
SQL> insert into txn (type,id) values (100,1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into txn (type,id) values (400,1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into txn (type,id) values (400,1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into txn (type,id) values (400,1);

1 row created.

SQL> commit;

Commit complete.

SQL> insert into txn (type,id) values (100,1);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00001: unique constraint (ALESSANDRO.TXN_MV_UNQ) violated


SQL>



Bye Alessandro
Re: Check Constraint [message #413307 is a reply to message #413297] Wed, 15 July 2009 03:45 Go to previous messageGo to next message
johnbach
Messages: 32
Registered: June 2009
Member
Thanks JRowbottom.

create unique index idxtxn on txn(case when type =100 then id else null end) 

Re: Check Constraint [message #413335 is a reply to message #413297] Wed, 15 July 2009 04:45 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, when tried I got the error UNIQUE CONSTRAINT VIOLATED


create table test_012 (col_1  number);

alter table test_012 add (constraint test_012_ck check (col_1 in (100,400)));

create unique index test_012_uq_idx on test_012(case when col_1 =400 then null else col_1 end);

insert into test_012 values (100);
insert into test_012 values (100);
insert into test_012 values (400);
insert into test_012 values (400);
insert into test_012 values (400);
insert into test_012 values (400);





Re: Check Constraint [message #413340 is a reply to message #413335] Wed, 15 July 2009 05:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes you did.

I posted an example that demonstrates that you can have as many 400s as you like, and only one 100.
If you can think of a way of doing this that shows the restricion on rows with a value of 100, and doesn't raise an exception, do please enlighten us.
Re: Check Constraint [message #413384 is a reply to message #413300] Wed, 15 July 2009 08:05 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I've always done this with a unique index and a decode or case as discussed by JRowbottom, and I may still prefer that method for the time being, but I'd never considered the MV example and found it to be an interesting example.

Also there are times when two tables would not be appropriate. Consider the case of a lookup table of some kind with entries that can come and go over time, for which history must be preserved. There is a status field with values of Active or Retired, and although there can be many entries that are retired, only one can be Active.
Previous Topic: How to calculate average time
Next Topic: how to assign a value for CLOB in Oracle 8i
Goto Forum:
  


Current Time: Sat Feb 15 00:08:26 CST 2025