Altering Tables

You can change the structure of existing tables with the ALTER TABLE statement. In the previous section of the tutorial, you created a simple Department table. Now you can use the ALTER TABLE statement to alter to this table. The syntax for altering a table, in simplified form, is:

ALTER TABLE table_name operation [, operation]

where each operation is one of the following:

ADD column

ADD tableconstraint

ALTER [column] col_name <alt_col_clause>

DROP column

DROP CONSTRAINT constraintname

Notice that you can drop a constraint only if you gave it a name at the time you created it.

Altering the Department Table

You now add five new columns (head_dept, mngr_no, budget, location, and phone_no) and two foreign key constraints to the Department table that you created earlier.

1. In Interactive SQL, type the following code and then execute it:

ALTER TABLE Department

ADD head_dept DEPTNO,

ADD mngr_no EMPNO,

ADD budget BUDGET,

ADD location VARCHAR(15),

ADD phone_no PHONENUMBER DEFAULT '555-1234',

ADD FOREIGN KEY (mngr_no)

REFERENCES Employee (emp_no) ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT fkdept FOREIGN KEY (head_dept)

REFERENCES Department (dept_no) ON DELETE CASCADE ON UPDATE CASCADE>

2. In IBConsole, click on the Department table once again and then Database > View Metadata to see the new table definition.

Advance To:

More Troubleshooting