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

1. Oracle PL / SQL
2. Constraints
3. Unique

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