ALTER Table

After you've created a table, you'll often find you need to adjust its structure — perhaps you forgot to declare a key, need a new column, or want to tighten up the rules on an existing one. The ALTER TABLE statement lets you make exactly these kinds of structural changes to an existing table, without dropping it and starting over. …

Sometimes a table is created without declaring a primary key up front — this is exactly what happened with the GUARDIAN and ATTENDANCE tables in Activity 9.4, where you were asked to create them without any constraints. ALTER TABLE lets you add that missing primary key afterwards.

Syntax

ALTER TABLE table_name ADD PRIMARY KEY (attribute_name);

For a composite primary key (a key made of two or more attributes together), list every attribute inside the parentheses, separated by commas.

Adding a single-column primary key

The GUARDIAN relation's primary key is the single attribute GUID (see Table 9.4). To add it:

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

Adding a composite primary key

The ATTENDANCE relation's primary key is composite — it needs both AttendanceDate and RollNumber together (see Table 9.5) to uniquely identify a row, since the same student can have many attendance records, one per date:

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

Once a relation's primary key is in place, the next step is to wire up any foreign keys it needs — the links that connect it to other relations. Before you can add a foreign key, three conditions must all be true:

• The table being referenced (the one the foreign key points to) must already exist.
• The referenced attribute(s) must already be part of that table's primary key.
• The data type and size of the referencing attribute must exactly match the referenced attribute.

Syntax

ALTER TABLE table_name ADD FOREIGN KEY(attribute_name)
REFERENCES referenced_table_name (attribute_name);

Example: linking STUDENT to GUARDIAN

Table 9.3 shows that GUID in STUDENT is a foreign key — it refers back to GUID in GUARDIAN. That makes STUDENT the referencing table and GUARDIAN the referenced table (the relationship is shown in Figure 8.4 of the previous chapter):

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

A UNIQUE constraint stops two rows from ever sharing the same value in a given column. You can add it to a column after the table already exists.

Syntax

ALTER TABLE table_name ADD UNIQUE (attribute_name);

Example: no two guardians share a phone number

Table 9.4 specifies that GPhone in GUARDIAN should be UNIQUE (a guardian's phone number should not repeat across different guardian records):

mysql> ALTER TABLE GUARDIAN …

Sometimes the original design of a table turns out to be incomplete, and you need to add a column that was never there in the first place. ALTER TABLE ... ADD handles this.

Syntax

ALTER TABLE table_name ADD attribute_name DATATYPE;

Example: the school adds an income column

Suppose the school decides to award scholarships based on a guardian's income — but the GUARDIAN table (Table 9.4) was never designed with an income column. To support this, a new income attribute of type INT is added:

mysql> ALTER TABLE GUARDIAN
    -> ADD income INT;
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0
``` …

If a column's data type turns out to be too restrictive — for instance, a VARCHAR length that's too short for real data — you can change it using MODIFY.

Syntax

ALTER TABLE table_name MODIFY attribute_name DATATYPE;

Example: widening GAddress

The GAddress attribute in GUARDIAN (Table 9.4) was originally declared VARCHAR(30). Suppose addresses are turning out to be longer than that in practice — the size can be increased to VARCHAR(40):

mysql> ALTER TABLE GUARDIAN …

By default, MySQL allows every attribute to hold NULL — except whichever attribute is the primary key. If you later decide a particular column should never be left empty, you can tighten that with MODIFY ... NOT NULL.

Syntax

ALTER TABLE table_name MODIFY attribute_name DATATYPE NOT NULL;

Example: making SName mandatory

To ensure every row in STUDENT has a name (no blank SName values):

mysql> ALTER TABLE STUDENT …

A DEFAULT value is what MySQL fills in automatically for a column when a row is inserted without specifying that column's value. You can add this to an existing attribute with MODIFY.

Syntax

ALTER TABLE table_name MODIFY attribute_name DATATYPE
DEFAULT default_value;

Example: a default date of birth

To set 15th May 2000 as the default SDateofBirth for any STUDENT row that doesn't specify one:

mysql> ALTER TABLE STUDENT
    -> MODIFY SDateofBirth DATE DEFAULT '2000-05-15';
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0
``` …

Just as you can add a column, you can also remove one that's no longer needed, using ALTER TABLE ... DROP.

Syntax

ALTER TABLE table_name DROP attribute_name;

Example: removing the income column

To undo the income attribute that was added to GUARDIAN back in part (D):

mysql> ALTER TABLE GUARDIAN
    -> DROP income; …

If a table's primary key needs to be redefined — say, changed to a different attribute, or made composite — you first have to remove the existing one.

Syntax

ALTER TABLE table_name DROP PRIMARY KEY;

Example: dropping GUARDIAN's primary key

mysql> ALTER TABLE GUARDIAN
    -> DROP PRIMARY KEY;
Query OK, 0 rows affected (0.72 sec)
Records: 0  Duplicates: 0  Warnings: 0
``` …