MySQL Tutorial/Table/Alter Column

Add unique constraint to a table using the alter table command

mysql>
mysql> CREATE TABLE myTable
    -> (
    ->    OrderID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID SMALLINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> desc myTable;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| OrderID | smallint(5) unsigned | NO   | PRI |         |       |
| ModelID | smallint(5) unsigned | NO   |     |         |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql>
mysql> ALTER TABLE myTable
    -> ADD UNIQUE (OrderID, ModelID);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> drop table myTable;
Query OK, 0 rows affected (0.00 sec)
mysql>

Changing a Column Name

mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     VARCHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> ALTER TABLE employee
    -> CHANGE First_Name FirstName VARCHAR(20);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> DESCRIBE employee;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| FirstName   | varchar(20) | YES  |     | NULL    |       |
| last_name   | varchar(15) | YES  |     | NULL    |       |
| start_date  | date        | YES  |     | NULL    |       |
| end_date    | date        | YES  |     | NULL    |       |
| salary      | float(8,2)  | YES  |     | NULL    |       |
| city        | varchar(10) | YES  |     | NULL    |       |
| description | varchar(15) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
8 rows in set (0.01 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql>

Changing a Column Type

mysql>
mysql>
mysql> CREATE TABLE Employee(
    ->     id            int,
    ->     first_name    VARCHAR(15),
    ->     last_name     CHAR(15),
    ->     start_date    DATE,
    ->     end_date      DATE,
    ->     salary        FLOAT(8,2),
    ->     city          VARCHAR(10),
    ->     description   VARCHAR(15)
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> DESCRIBE employee;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| first_name  | varchar(15) | YES  |     | NULL    |       |
| last_name   | char(15)    | YES  |     | NULL    |       |
| start_date  | date        | YES  |     | NULL    |       |
| end_date    | date        | YES  |     | NULL    |       |
| salary      | float(8,2)  | YES  |     | NULL    |       |
| city        | varchar(10) | YES  |     | NULL    |       |
| description | varchar(15) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> ALTER TABLE employee
    -> CHANGE Last_Name Last_Name VARCHAR(50);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql>
mysql> DESCRIBE employee;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| first_name  | varchar(15) | YES  |     | NULL    |       |
| Last_Name   | varchar(50) | YES  |     | NULL    |       |
| start_date  | date        | YES  |     | NULL    |       |
| end_date    | date        | YES  |     | NULL    |       |
| salary      | float(8,2)  | YES  |     | NULL    |       |
| city        | varchar(10) | YES  |     | NULL    |       |
| description | varchar(15) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
8 rows in set (0.02 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>