modbsql5

Altering Tables

Tables can be altered in one of two ways:

by adding a column to a table which was previously defined

by changing an existing column's definition or data type.

Adding a column is easy and much like the CREATE table command. The command below shows how to add a 'test' column to the employee table:

SQL> ALTER TABLE EMPLOYEE ADD

(TEST CHAR (2));

A Table altered message will be returned.

Describe the employee table to confirm the existence of the new column.

SQL>DESCRIBE EMPLOYEE;

Name	Null?	Type
EMPNO	NOT NULL	NUMBER(4)
NAME	NOT NULL	CHAR(8)
JOB		CHAR(4)
SALARY	NOT NULL	NUMBER(8,2)
COMM		NUMBER(8,2)
DEPTNO	NOT NULL	NUMBER(4)
SEX		CHAR(1)
TEST		CHAR(2)

The column TEST was added to the table as a null column for 2 characters. If you view the rows in the table you will see that all the rows are null for the TEST column.

If you had tried to specify NOT NULL for the column you would have gotten an error. Why? Because a column defined as not null has to have a value in it and by adding the column to the table that effectively adds the column to each row without a value.

How do we get around this? First add the column to the table as null, then fill the column with data for every row. Next, alter the table again with 'modify' rather than 'add', changing the column definition to NOT NULL. To try this, first use the UPDATE command to fill the Test column with data.

SQL>UPDATE EMPLOYEE

SET TEST = 'No'

WHERE TEST IS NULL;

Then use the following command to change the column to NOT NULL.

SQL> ALTER TABLE EMPLOYEE MODIFY

(TEST CHAR(2) NOT NULL );

In addition to the restrictions listed above for altering a table here are some rules for modifying a column:

You can increase a CHAR column width or the number of digits in a NUMBER column at any time.

You can also increase or decrease the decimal places specified for a NUMBER column at any time.

You may also alter a table to add a primary key after the table has been created. The syntax to add a primary key is as follows:

SQL>ALTER TABLE <table_name> ADD PRIMARY KEY (<column_names,…>);

If you have more than one column in the primary key, separate each column with a comma.

To change a column's definition or data type, use modify in the alter table command.

SQL>ALTER TABLE <table_name> MODIFY

(<column_name> datatype,…);

Last modified 11/4/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems

Return to ORACLE/SQL Tutorial Contents Page | Return to Using Indexes | Go to Dropping Tables