Alter table to drop constraint with name : Drop Constraint « Constraints « Oracle PL / SQL






Alter table to drop constraint with name

 


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

Table created.

SQL>
SQL>
SQL> desc
Usage: DESCRIBE [schema.]object[@db_link]
SQL>
SQL> insert into myTable values (4,   5);

1 row created.

SQL> insert into myTable values (2,   1);

1 row created.

SQL> insert into myTable values (2,   1);
insert into myTable values (2,   1)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C004357) 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
         2          1
         9          8
         6          9
                    9
                    9

6 rows selected.

SQL>
SQL> alter table myTable drop constraint sys_c004357;

Table altered.

SQL>
SQL>
SQL> insert into myTable values (4,   5);

1 row created.

SQL> insert into myTable values (2,   1);

1 row created.

SQL> insert into myTable values (2,   1);

1 row created.

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
         2          1
         9          8
         6          9
                    9
                    9
         4          5
         2          1
         2          1
         9          8
         6          9

         A          B
---------- ----------
                    9
                    9

13 rows selected.

SQL>
SQL> drop table myTable;
           
         
  








Related examples in the same category

1.Drop a UNIQUE constraint without specifying its name by referencing the column or columns
2.Drop Unique Constraint and Add Primary Key