Oracle PL/SQL/Constraints/Enable Disable Primary Key

Disable a Primary Key

SQL> -- Disable a Primary Key
SQL>
SQL> CREATE TABLE supplier
  2  (  supplier_id     numeric(10)     not null,
  3     supplier_name   varchar2(50)    not null,
  4     contact_name    varchar2(50)
  5  );
Table created.
SQL>
SQL> desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)
SQL>
SQL> ALTER TABLE supplier
  2  add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);
Table altered.
SQL>
SQL> desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)
SQL>
SQL> ALTER TABLE supplier
  2  disable CONSTRAINT supplier_pk;
Table altered.
SQL>
SQL> desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)
SQL>
SQL> drop table supplier;
Table dropped.
SQL>
SQL>

Enable a primary key

SQL>
SQL> CREATE TABLE supplier
  2  (  supplier_id     numeric(10)     not null,
  3     supplier_name   varchar2(50)    not null,
  4     contact_name    varchar2(50)
  5  );
Table created.
SQL>
SQL> desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)
SQL>
SQL> ALTER TABLE supplier
  2  add CONSTRAINT supplier_pk PRIMARY KEY (supplier_id);
Table altered.
SQL>
SQL> desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)
SQL>
SQL> ALTER TABLE supplier
  2  disable CONSTRAINT supplier_pk;
Table altered.
SQL>
SQL> desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)
SQL>
SQL> ALTER TABLE supplier
  2  enable CONSTRAINT supplier_pk;
Table altered.
SQL>
SQL> desc supplier;
 Name                                                                                                  Null?    Type
 ----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
 SUPPLIER_ID                                                                                           NOT NULL NUMBER(10)
 SUPPLIER_NAME                                                                                         NOT NULL VARCHAR2(50)
 CONTACT_NAME                                                                                           VARCHAR2(50)
SQL>
SQL> drop table supplier;
Table dropped.
SQL>
SQL>
SQL>