Learn online DBMS “Alter Table Command” Complete Lesson 10

ALTER TABLE COMMAND– This command is used to modify the base table definition. The modifications that can be done using this command are:

1. Adding a column: Suppose we want to add a column Date_of_Birth in the Teacher table. Following command is used to add the column:

ALTER TABLE Teacher ADD Date_of_Birth date;

Teacher table is shown before and after the modification in the following figure:

Dropping a column: A column can be dropped using this command but one must specify the options (RESTRICT or CASCADE) for the drop behavior. As discussed earlier, RESTRICT would not let the column be dropped if it is being referenced in other tables and CASCADE would drop the constraint as well as all the constraints that refer this column would also be dropped.

ALTER TABLE Teacher DROP Dept_No CASCADE;

This will drop the Dept_No column in the Teacher Table and it would also drop the foreign key constraint TEACHER_FK as it uses this column.

Note that as stated earlier, RESTRICT and CASCADE options are not supported in MySQL server 5.6.14. Hence it will not allow todrop a column if it is referred anywhere else. The MySQL command to drop Subject Column from Teacher is shown below:

Altering a Column: A column definition can also be altered. For example – dropping the default value or defining a new default value. For example, in the Teacher table the default value of Dept_No is 234. If you want to drop this default value or change this value to 345 then it can be done by using the following commands:

ALTER TABLE Teacher ALTER Dept_No DROP DEFAULT;

ALTER TABLE Teacher ALTER Dept_No SET DEFAULT 456;

Dropping keys: A foreign key/primary key/key can be dropped by using ALTER TABLE command. For example if you want to delete the foreign key TEACHER_FK in the Teacher table then following command can be used:

ALTER TABLE Teacher DROP FOREIGN KEY TEACHER_FK;

CASCADE or RESTRICT option can be specified for the drop behavior (though not supported in MySQL server 5.6).

Primary key can be dropped by using the command:

ALTER TABLE Teacher DROP PRIMARY KEY TEACHER_PK;

However Primary key cannot be removed if it is the only primary key of the table. Hence the above command will result in an error.

Adding a Constraint: For example if you want to add the foreign key constraint TEACHER_FK back then the command would be:

ALTER TABLE Teacher ADD CONSTRAINT FOREIGN KEYTEACHER_FK FOREIGN KEY (Dept_No) REFERENCES Department(Dept_ID) ON DELETE SET NULL ON UPDATE SET NULL;