SQL/MySQL/Table Index/Alter Table

Altering and Dropping Tables

Drop table Stock;
CREATE TABLE Stock
(
   ID SMALLINT
);
Describe Stock;
ALTER TABLE Stock
ADD COLUMN Quantity SMALLINT UNSIGNED NOT NULL,
MODIFY ID SMALLINT UNSIGNED NOT NULL,
ADD PRIMARY KEY (ID);
Describe Stock;
ALTER TABLE Stock
DROP COLUMN Quantity,
DROP PRIMARY KEY;
Describe Stock;

Altering Database Tables: Add a Column

/*
mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) PRIMARY KEY NOT NULL,
    ->    PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
    ->    Age     INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.09 sec)
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) | YES  |     | Unknown Phone |       |
| Age     | int(11)     | YES  |     | NULL          |       |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.01 sec)

mysql> Select * from Employee;
+----------+---------------+------+
| Name     | PhoneNo       | Age  |
+----------+---------------+------+
| John Doe | Unknown Phone |   31 |
+----------+---------------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE Employee ADD Address VARCHAR(50);
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) | YES  |     | Unknown Phone |       |
| Age     | int(11)     | YES  |     | NULL          |       |
| Address | varchar(50) | YES  |     | NULL          |       |
+---------+-------------+------+-----+---------------+-------+
4 rows in set (0.00 sec)
mysql> Select * from Employee;
+----------+---------------+------+---------+
| Name     | PhoneNo       | Age  | Address |
+----------+---------------+------+---------+
| John Doe | Unknown Phone |   31 | NULL    |
+----------+---------------+------+---------+
1 row in set (0.00 sec)

*/
Drop TABLE Employee;
       
CREATE TABLE Employee (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL, 
   PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
   Age     INT CHECK (Age BETWEEN 20 and 30));
Describe Employee;
INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26);
INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31);
Select * from Employee;
       
ALTER TABLE Employee ADD Address VARCHAR(50);
Describe Employee;
Select * from Employee;

Altering Database Tables: Add More Columns

/*
mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) PRIMARY KEY NOT NULL,
    ->    PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
    ->    Age     INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.12 sec)
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) | YES  |     | Unknown Phone |       |
| Age     | int(11)     | YES  |     | NULL          |       |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.00 sec)

mysql> Select * from Employee;
+----------+---------------+------+
| Name     | PhoneNo       | Age  |
+----------+---------------+------+
| John Doe | Unknown Phone |   31 |
+----------+---------------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE Employee ADD (EMail VARCHAR(25), ICQ VARCHAR(15));
Query OK, 1 row affected (0.08 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) | YES  |     | Unknown Phone |       |
| Age     | int(11)     | YES  |     | NULL          |       |
| EMail   | varchar(25) | YES  |     | NULL          |       |
| ICQ     | varchar(15) | YES  |     | NULL          |       |
+---------+-------------+------+-----+---------------+-------+
5 rows in set (0.00 sec)
mysql> Select * from Employee;
+----------+---------------+------+-------+------+
| Name     | PhoneNo       | Age  | EMail | ICQ  |
+----------+---------------+------+-------+------+
| John Doe | Unknown Phone |   31 | NULL  | NULL |
+----------+---------------+------+-------+------+
1 row in set (0.01 sec)

*/
Drop TABLE Employee;
       
CREATE TABLE Employee (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL, 
   PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
   Age     INT CHECK (Age BETWEEN 20 and 30));
Describe Employee;
INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26);
INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31);
Select * from Employee;
  
ALTER TABLE Employee ADD (EMail VARCHAR(25), ICQ VARCHAR(15));
Describe Employee;
Select * from Employee;

Altering Database Tables: Drop a Column

/*
mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) PRIMARY KEY NOT NULL,
    ->    PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
    ->    Age     INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.14 sec)
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) | YES  |     | Unknown Phone |       |
| Age     | int(11)     | YES  |     | NULL          |       |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.01 sec)

mysql> Select * from Employee;
+----------+---------------+------+
| Name     | PhoneNo       | Age  |
+----------+---------------+------+
| John Doe | Unknown Phone |   31 |
+----------+---------------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE Employee DROP COLUMN PhoneNo;
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> Describe Employee;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Name  | varchar(50) |      | PRI |         |       |
| Age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> Select * from Employee;
+----------+------+
| Name     | Age  |
+----------+------+
| John Doe |   31 |
+----------+------+
1 row in set (0.00 sec)

*/
Drop TABLE Employee;
       
CREATE TABLE Employee (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL, 
   PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
   Age     INT CHECK (Age BETWEEN 20 and 30));
Describe Employee;
INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26);
INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31);
Select * from Employee;
  
ALTER TABLE Employee DROP COLUMN PhoneNo;
Describe Employee;
Select * from Employee;

Altering Database Tables: Drop Unique Constraint and Add Primary Key

/*
mysql> CREATE TABLE Employee (
    ->    Name    VARCHAR(50) PRIMARY KEY NOT NULL,
    ->    PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
    ->    Age     INT CHECK (Age BETWEEN 20 and 30));
Query OK, 0 rows affected (0.04 sec)
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) | YES  |     | Unknown Phone |       |
| Age     | int(11)     | YES  |     | NULL          |       |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.01 sec)
mysql> Select * from Employee;
+----------+---------------+------+
| Name     | PhoneNo       | Age  |
+----------+---------------+------+
| John Doe | Unknown Phone |   31 |
+----------+---------------+------+
1 row in set (0.00 sec)
mysql> ALTER TABLE Employee DROP INDEX Name;
ERROR 1091 (42000): Can"t DROP "Name"; check that column/key exists
mysql> ALTER TABLE Employee ADD PRIMARY KEY (Name);
ERROR 1068 (42000): Multiple primary key defined
mysql> Describe Employee;
+---------+-------------+------+-----+---------------+-------+
| Field   | Type        | Null | Key | Default       | Extra |
+---------+-------------+------+-----+---------------+-------+
| Name    | varchar(50) |      | PRI |               |       |
| PhoneNo | varchar(15) | YES  |     | Unknown Phone |       |
| Age     | int(11)     | YES  |     | NULL          |       |
+---------+-------------+------+-----+---------------+-------+
3 rows in set (0.00 sec)
*/
Drop TABLE Employee;
       
CREATE TABLE Employee (
   Name    VARCHAR(50) PRIMARY KEY NOT NULL, 
   PhoneNo VARCHAR(15) DEFAULT "Unknown Phone",
   Age     INT CHECK (Age BETWEEN 20 and 30));
Describe Employee;
INSERT INTO Employee (Name, Phone, Age) VALUES ("Joe Wang", "666 2323", 26);
INSERT INTO Employee (Name, Age) VALUES ("John Doe", 31);
Select * from Employee;
  
  
ALTER TABLE Employee DROP INDEX Name;
ALTER TABLE Employee ADD PRIMARY KEY (Name);
Describe Employee;

Alter table: add primary key, foreign key and add column

Drop table Books;
CREATE TABLE Books
(
   ID SMALLINT NOT NULL,
   Name VARCHAR(40) NOT NULL,
   PubID SMALLINT NOT NULL DEFAULT "Unknown"
)
ENGINE=INNODB;
Describe Books;
  
ALTER TABLE Books
ADD PRIMARY KEY (BookID),
ADD CONSTRAINT fk_1 FOREIGN KEY (PubID) REFERENCES Publishers (PubID),
ADD COLUMN Format ENUM("paperback", "hardcover") NOT NULL AFTER Name;
Describe Books;

Alter table: add unique

/*
mysql> Drop table Product;
mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID SMALLINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> ALTER TABLE Product
    -> ADD UNIQUE (ID, ModelID);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      |     | 0       |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

*/
Drop table Product;

CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ModelID SMALLINT UNSIGNED NOT NULL
);
ALTER TABLE Product
ADD UNIQUE (ID, ModelID);
Describe Product;

Alter table: drop primary and foreign key

/*
mysql> Drop table Books;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE Books
    -> (
    ->    ID SMALLINT NOT NULL,
    ->    Name VARCHAR(40) NOT NULL,
    ->    PubID SMALLINT NOT NULL DEFAULT "Unknown"
    -> )
    -> ENGINE=INNODB;
Query OK, 0 rows affected (0.17 sec)
mysql> Describe Books;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | smallint(6) |      |     | 0       |       |
| Name  | varchar(40) |      |     |         |       |
| PubID | smallint(6) |      |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE Books
    -> DROP PRIMARY KEY,
    -> DROP FOREIGN KEY fk_1,
    -> DROP COLUMN Format;
ERROR 1091 (42000): Can"t DROP "PRIMARY"; check that column/key exists
mysql> Describe Books;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| ID    | smallint(6) |      |     | 0       |       |
| Name  | varchar(40) |      |     |         |       |
| PubID | smallint(6) |      |     | 0       |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

*/
Drop table Books;
CREATE TABLE Books
(
   ID SMALLINT NOT NULL,
   Name VARCHAR(40) NOT NULL,
   PubID SMALLINT NOT NULL DEFAULT "Unknown"
)
ENGINE=INNODB;
Describe Books;
  
ALTER TABLE Books
DROP PRIMARY KEY,
DROP FOREIGN KEY fk_1,
DROP COLUMN Format;
Describe Books;