if a column is not explicitely defined as not null, nulls can be inserted multiple times : Unique « Constraints « Oracle PL / SQL






if a column is not explicitely defined as not null, nulls can be inserted multiple times

  


SQL> --Unique Key
SQL>
SQL>
SQL> create table myTable (
  2    a number unique,
  3    b number
  4  );

Table created.

SQL>
SQL> desc myTable;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------

 A                                                                                                      NUMBER
 B                                                                                                      NUMBER

SQL>
SQL> -- if a column is not explicitely defined as not null, nulls can be inserted multiple times:
SQL>
SQL> insert into myTable values (4,   5);

1 row created.

SQL> insert into myTable values (4,   1);
insert into myTable values (4,   1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C004353) violated


SQL> insert into myTable values (9,   8);

1 row created.

SQL> insert into myTable values (6,   9);

1 row created.

SQL> insert into myTable values (null,9);

1 row created.

SQL> insert into myTable values (null,9);

1 row created.

SQL>
SQL> select * from myTable;

         A          B
---------- ----------
         4          5
         9          8
         6          9
                    9
                    9

SQL>
SQL> drop table myTable;
           
         
    
  








Related examples in the same category

1.Vialate the unique contraint: try to insert the same value
2.A unique constraint can be extended over multiple columns
3.Add unique containt to a varchar2 type column
4.Create a table with 'unique deferrable initially immediate'
5.Add unique constraints
6.ORA-00001: unique constraint (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated
7.Setting a Unique Constraint
8.Unique value column