Create logic constraints

Table t1(c1,c2), c1 not null; if c2 is null then c1 c2 can be identical,

if c2 not null then c1,c2 must be unique;

SQL> create table t1 (c1 varchar2(10),c2 varchar2(10));

Table created.

SQL> alter table t1 modify(c1 not null);

Table altered.

SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 NOT NULL VARCHAR2(10)
C2 VARCHAR2(10)

SQL> create unique index uq_idx_t1 on t1(case when c2 is not null then c1||c2 end);

Index created.

SQL> insert into t1
2 values('a',null);

1 row created.

SQL> insert into t1
2 values('a',null);

1 row created.

SQL> insert into t1
2 values('a','b');

1 row created.

SQL> insert into t1
2 values('a','b');
insert into t1
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.UQ_IDX_T1) violated

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/45259/viewspace-134927/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/45259/viewspace-134927/