Check Constraint [message #413296] |
Wed, 15 July 2009 03:06  |
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   |
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   |
 |
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 #413335 is a reply to message #413297] |
Wed, 15 July 2009 04:45   |
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   |
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  |
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.
|
|
|