MySQL Tutorial/Table/Alter Column

Add unique constraint to a table using the alter table command

   <source lang="sql">

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></source>

Changing a Column Name

   <source lang="sql">

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></source>

Changing a Column Type

   <source lang="sql">

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></source>