Oracle PL/SQL/Constraints/Enable Disable Primary Key
Disable a Primary Key
<source lang="sql">
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>
</source>
Enable a primary key
<source lang="sql">
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>
</source>
