Oracle 11g: How to add/enable/disable/drop a constraint

 Table Sample:

SQL> desc orders ; Name                                      Null?    Type ----------------------------------------- -------- ---------------------------- ORDER_ID                                  NOT NULL NUMBER(12) ORDER_DATE                                         DATE CUSTOMER_ID                               NOT NULL NUMBER(6) ORDER_TOTAL                                        NUMBER(8,2) SALES_REP_ID                                       NUMBER(6)

1. In-line

alter table orders modify ORDER_DATE not null ; 

'NOT NULL Can only be created by in-line mode.

alter table orders modify ORDER_DATE null ;

'NOT NULL Can only be droped by in-line mode.

alter table orders modify ORDER_DATE unique ;

alter table orders modify constraint SYS_C0011140 disable ;

alter table orders modify constraint SYS_C0011140 enable ; 

alter table orders drop constraint SYS_C0011138 ;

2. Out-of-line

alter table orders add constraint UN_ORDER_DATE unique(order_date) ;

 

alter table orders disable constraint UN_ORDER_DATE ;

alter table orders enable constraint UN_ORDER_DATE ;

 

alter table orders drop constraint UN_ORDER_DATE ;

3.Query

select * from user_constraints where table_name='ORDERS' ;