Oracle Database DDL Statements – ALTER Commands

Oracle Database Data Definition Language (DDL Statements) – ALTER Commands:

In this section, we will try to explain about important database ALTER commands that are used by a data modeler by relating it with our example data.

ALTER TABLE – Add Column:

 ALTER TABLE EMPLOYEE_DTL ADD JOIN_DATE DATE; 

ALTER TABLE – Rename Column:

 ALTER TABLE EMPLOYEE_DTL RENAME column JOIN_DATE TO EMP_JOIN_DT; 

ALTER TABLE – Modify column’s Data Type:

 ALTER TABLE EMPLOYEE_DTL MODIFY EMP_JOIN_DT VARCHAR2(10); 

ALTER TABLE – Drop Column:

 ALTER TABLE EMPLOYEE_DTL DROP COLUMN EMP_JOIN_DT; 

ALTER TABLE – Add Check Constraint:

ALTER TABLE EMPLOYEE_DTL ADD CONSTRAINT CH_SAL CHECK(SLRY_AMT BETWEEN 4000 AND 7000); 

ALTER TABLE – Add Unique Constraint:

ALTER TABLE EMPLOYEE_DTL ADD CONSTRAINT UN_SSN UNIQUE(SSN); 

ALTER TABLE – Disable/Enable/Drop Constraint:

ALTER TABLE EMPLOYEE_DTL DISABLE CONSTRAINT UN_SSN; 

ALTER TABLE EMPLOYEE_DTL ENABLE CONSTRAINT UN_SSN;

ALTER TABLE EMPLOYEE_DTL DROP CONSTRAINT UN_SSN; 

ALTER TABLE – Modify Constraint:

 ALTER TABLE EMPLOYEE_DTL MODIFY SLRY_AMT NUMBER(7,2) NULL;

 ALTER TABLE EMPLOYEE_DTL MODIFY SLRY_AMT NUMBER(7,2) NOT NULL; 

Leave a Reply

Your email address will not be published. Required fields are marked *