A table has a, b two fields, b have come into effect and the failure of two state, to ensure the b effect for the state, a field is the only, is there a database solution, failure state is likely to be multiple, so ab joint is not the only one

CodePudding user response:

 
SQL> The create table t (int a, b, int); 
The Table created 
SQL> Create unique index iux_t_ab on t (decode) (b, 0, null, a); 
The Index created 
SQL> - b=0, there can be a duplicate values 
SQL> Insert into t values (100, 1); 
1 row inserted 
SQL> Insert into t values (100, 0); 
1 row inserted 
SQL> Insert into t values (100, 0); 
1 row inserted 
SQL> Insert into t values (200, 1); 
1 row inserted 
SQL> Insert into t values (200, 1); - the line failure 
Insert into t values (200, 1) 
ORA - 00001: a violation of the constraints (ORACLE. IUX_T_AB) 
SQL> Insert into t values (200, 0); 
1 row inserted 
SQL> Select * from t; 
A, B 
-- -- -- 
100 1 
100 
100 
200 1 
200 
SQL> Drop table t purge; 
Table dropped 

SQL> 

CodePudding user response:

The data quantity is little
Select * from table where a in (
Select from table where b='effective' group by having a count (*)=1);

Data volume, the subquery built temporary table