ALTER Table

A table's structure is rarely perfect on the first attempt. After creating a table you may realise that an attribute needs to be added or removed, that the data type of an existing attribute must change, or that a constraint should be attached to an attribute. In all such cases the structure of the table is changed with the ALTER statement — no need to drop and rebuild the table.

ALTER TABLE tablename ADD/MODIFY/DROP attribute1, attribute2,..

The three verbs cover every kind of structural change: ADD introduces something new (an attribute, a key, a constraint), MODIFY changes what an existing attribute is, and DROP removes something. Before working through the operations below, create the other two relations GUARDIAN and ATTENDANCE as per the data types in Tables 8.4 and 8.5, without adding any constraint (Activity 8.4) — the ALTER statements will then supply the constraints.

(A) Add a primary key to a relation. The GUARDIAN table gets its primary key on GUID:

mysql> ALTER TABLE GUARDIAN ADD PRIMARY KEY (GUID);
Query OK, 0 rows affected (1.14 sec)

For ATTENDANCE the primary key is a composite key made up of two attributes — AttendanceDate and RollNumber — so both are listed together:

mysql> ALTER TABLE ATTENDANCE
    -> ADD PRIMARY KEY(AttendanceDate,
    -> RollNumber);
Query OK, 0 rows affected (0.52 sec)

(B) Add a foreign key to a relation. Once primary keys are in place, the next step is to add the foreign keys (if any). A relation may have multiple foreign keys, and each foreign key is defined on a single attribute. Three conditions must hold before a foreign key can be added:

• the referenced relation must already have been created;
• the referenced attribute must be part of the primary key of the referenced relation;
• the data types and sizes of the referenced and referencing attributes must be the same.

ALTER TABLE table_name
ADD FOREIGN KEY(attribute name)
REFERENCES referenced_table_name (attribute name);

In STUDENT, the attribute GUID (the referencing attribute) is a foreign key referring to GUID (the referenced attribute) of GUARDIAN — so STUDENT is the referencing table and GUARDIAN is the referenced table:

mysql> ALTER TABLE STUDENT
    -> ADD FOREIGN KEY(GUID) REFERENCES
    -> GUARDIAN(GUID);
Query OK, 0 rows affected (0.75 sec)

As practice (Activity 8.5), add the foreign key in the ATTENDANCE table, identifying the referencing and referenced tables from the database schema. And a question worth answering for yourself: which are the foreign keys in ATTENDANCE and STUDENT, and does GUARDIAN have any foreign key at all?

(C) Add the UNIQUE constraint to an existing attribute. In GUARDIAN, GPhone carries the constraint UNIQUE — no two values in that column may be the same:

ALTER TABLE table_name ADD UNIQUE (attribute name);

mysql> ALTER TABLE GUARDIAN
    -> ADD UNIQUE(GPhone);
Query OK, 0 rows affected (0.44 sec)

(D) Add an attribute to an existing table.

ALTER TABLE table_name ADD attribute_name DATATYPE;

Suppose the school principal decides to award scholarships to needy students, for which the guardian's income must be known — but no income attribute was ever kept in GUARDIAN. The database designer now adds a new attribute income of data type INT:

mysql> ALTER TABLE GUARDIAN
    -> ADD income INT;
Query OK, 0 rows affected (0.47 sec)

(Think about it: given that the data type is INT, what are the minimum and maximum income values that could be entered?)

(E) Modify the data type of an attribute.

ALTER TABLE table_name MODIFY attribute DATATYPE;

To enlarge GAddress of GUARDIAN from VARCHAR(30) to VARCHAR(40):

mysql> ALTER TABLE GUARDIAN
    -> MODIFY GAddress VARCHAR(40);
Query OK, 0 rows affected (0.11 sec)

(F) Modify the constraint of an attribute. When a table is created, each attribute takes NULL by default except the primary key. An attribute's constraint can be changed from NULL to NOT NULL:

ALTER TABLE table_name MODIFY attribute DATATYPE NOT NULL;

To make SName of STUDENT mandatory:

mysql> ALTER TABLE STUDENT
    -> MODIFY SName VARCHAR(20) NOT NULL;
Query OK, 0 rows affected (0.47 sec)

(G) Add a default value to an attribute.

ALTER TABLE table_name MODIFY attribute DATATYPE DEFAULT default_value;

To set the default value of SDateofBirth in STUDENT to 15th May 2000:

mysql> ALTER TABLE STUDENT
    -> MODIFY SDateofBirth DATE DEFAULT
    -> 2000-05-15; …