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>