Table data COL1 COL2
A
A
B 1
B 1

The only constraint two fields, one field is not empty, A field can be null, to achieve uniqueness constraints, not empty data only allowed in the above example of A situation, the situation does not allow B, B data there are exist, ignore the original duplicate data, trouble you are not allowed to appear after Daniel

CodePudding user response:

Nobody know me

CodePudding user response:

 
Write you a example 
 -
SQL> 
SQL> Create table test (a varchar (10) not null, b varchar (10)); 
The Table created 
SQL> The begin 
2 insert into the test values (' A ', NULL); 
3 the insert into the test values (' A ', NULL); 
4 the insert into the test values (' B ', '1'); 
5 the insert into the test values (' B ', '1'); 
6 the end; 
7/
PL/SQL procedure successfully completed 
SQL> The alter table test 
2 add constraint uk_test unique (a, b) deferrable disable novalidate; 
Table altered 
SQL> The alter table test enable novalidate constraint uk_test; 
Table altered 
SQL> Insert into test values (' A ', NULL); 
Insert into test values (' A ', NULL) 
ORA - 00001: a violation of the constraints (ITSM_USER. UK_TEST) 
SQL> Commit; 
Commit complete 
SQL> Insert into test values (' C ', NULL); 
1 row inserted 
SQL> Commit; 
Commit complete 
SQL> Insert into test values (' C ', NULL); 
Insert into test values (' C ', NULL) 
ORA - 00001: a violation of the constraints (ITSM_USER. UK_TEST) 
SQL> Commit; 
Commit complete 
SQL> Select * from the test; 
A, B 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
A 
A 
B 1 
B 1 
C 
SQL> Drop table test purge; 
Table dropped 

SQL> 

CodePudding user response:

refer to the second floor wmxcn2000 response:

 
Write you a example 
 -
SQL> 
SQL> Create table test (a varchar (10) not null, b varchar (10)); 
The Table created 
SQL> The begin 
2 insert into the test values (' A ', NULL); 
3 the insert into the test values (' A ', NULL); 
4 the insert into the test values (' B ', '1'); 
5 the insert into the test values (' B ', '1'); 
6 the end; 
7/
PL/SQL procedure successfully completed 
SQL> The alter table test 
2 add constraint uk_test unique (a, b) deferrable disable novalidate; 
Table altered 
SQL> The alter table test enable novalidate constraint uk_test; 
Table altered 
SQL> Insert into test values (' A ', NULL); 
Insert into test values (' A ', NULL) 
ORA - 00001: a violation of the constraints (ITSM_USER. UK_TEST) 
SQL> Commit; 
Commit complete 
SQL> Insert into test values (' C ', NULL); 
1 row inserted 
SQL> Commit; 
Commit complete 
SQL> Insert into test values (' C ', NULL); 
Insert into test values (' C ', NULL) 
ORA - 00001: a violation of the constraints (ITSM_USER. UK_TEST) 
SQL> Commit; 
Commit complete 
SQL> Select * from the test; 
A, B 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
A 
A 
B 1 
B 1 
C 
SQL> Drop table test purge; 
Table dropped 

SQL> 

I have a try

CodePudding user response:

refer to the second floor wmxcn2000 response:

 
Write you a example 
 -
SQL> 
SQL> Create table test (a varchar (10) not null, b varchar (10)); 
The Table created 
SQL> The begin 
2 insert into the test values (' A ', NULL); 
3 the insert into the test values (' A ', NULL); 
4 the insert into the test values (' B ', '1'); 
5 the insert into the test values (' B ', '1'); 
6 the end; 
7/
PL/SQL procedure successfully completed 
SQL> The alter table test 
2 add constraint uk_test unique (a, b) deferrable disable novalidate; 
Table altered 
SQL> The alter table test enable novalidate constraint uk_test; 
Table altered 
SQL> Insert into test values (' A ', NULL); 
Insert into test values (' A ', NULL) 
ORA - 00001: a violation of the constraints (ITSM_USER. UK_TEST) 
SQL> Commit; 
Commit complete 
SQL> Insert into test values (' C ', NULL); 
1 row inserted 
SQL> Commit; 
Commit complete 
SQL> Insert into test values (' C ', NULL); 
Insert into test values (' C ', NULL) 
ORA - 00001: a violation of the constraints (ITSM_USER. UK_TEST) 
SQL> Commit; 
Commit complete 
SQL> Select * from the test; 
A, B 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
A 
A 
B 1 
B 1 
C 
SQL> Drop table test purge; 
Table dropped 

SQL> 

teacher, you are the 'A', NULL such data can't insert in

CodePudding user response:

A single value NULL to insert into, not an error,

But when the only constraint is compound field, the situation has changed, according to the description of the Oracle documentation, for complex field, the only constraint is not null field value can't be repeated, that is to say, if the two fields constitute a unique constraint, one of the fields is empty, then the value of another field cannot duplicate,

CodePudding user response:

reference 5 floor sych888 reply:

single value NULL can be inserted into, not an error,
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull