Create / modify / delete tables in MySQL

One of the frequent tasks during the project development process is the change of the structure of the database. This guide aims to show how to create, modify and delete tables from a MySQL database. However the sentence involved in these operations are extensive and complex and cover all options would be cumbersome and impractical. For more information refer to the official site of MySQL

Create table

Create hotel table with the following specification:

– id: integer unsigned, autoincrement, primary key
– name: variable length, 100 characters as maximum length and not null
– active: boolean, not null, default 1
– url: variable length, 255 characters as maximum length, unique index

CREATE TABLE hotel (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    active TINYINT(1) DEFAULT '1' NOT NULL,
    url VARCHAR(255),
    UNIQUE INDEX url_idx (url)) DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci ENGINE = InnoDB;

Note that we have specified for this table InnoDB storage engine and UTF-8 encoding.

Create the worker table with the following specification:

– id: integer unsigned, autoincrement, primary key
– name: variable length, 100 characters as maximum length and not null
– active: boolean, not null, default 1

CREATE worker TABLE (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    active TINYINT(1) DEFAULT '1' NOT NULL,
    hotel_id INT UNSIGNED ) DEFAULT CHARACTER SET utf8
    COLLATE utf8_general_ci ENGINE = InnoDB;

See table definition

DESC hotel;

See indexes for a table

SHOW INDEX FROM hotel;

Add column

Add the code column to the table worker

ALTER TABLE worker ADD COLUMN code CHAR(11) NOT NULL;

The ADD COLUMN statement adds the column at the end of the table, by default, if you want to add the column at a specific position use AFTER sentence, for example: add the worker personal identification after code column:

ALTER TABLE worker ADD COLUMN personal_id CHAR(11) NOT NULL AFTER code;

Change the encoding for a column

Despite of that the table has been created with UTF-8 encoding by default not all columns needed to have this type of coding, for example the most likely that worker code is built using ASCII characters therefore would modify the previous sentence:

ALTER TABLE worker ADD COLUMN code CHAR(11) 
 CHAR SET 'ascii' COLLATE 'ascii_general_ci' NOT NULL;

ALTER TABLE worker MODIFY COLUMN code CHAR(11) 
 CHAR SET 'ascii' COLLATE 'ascii_general_ci' NOT NULL;

Note: Remember that a utf8 column requires more memory and processing thant ASCII column then the above will be contributing to improve the performance of our application

Modify a column definition

Restrict the values of hotel_id to not null values in the worker table

ALTER TABLE worker MODIFY COLUMN hotel_id INT UNSIGNED NOT NULL;

Create index

The worker code must be unique

CREATE UNIQUE INDEX worker_code_idx USING BTREE
on worker(code);

Create relationship

We set the relationship between worker and hotel (a hotel can have many workers and a worker belongs to a single hotel). When a hotel is deleted all workers are removed too.

ALTER TABLE worker ADD CONSTRAINT worker_hotel_id_hotel_id
FOREIGN KEY (hotel_id) REFERENCES hotel(id) ON DELETE CASCADE;

Rename column

Change the name of the url column to slug from the hotel table and restrict values to not null and empty string as a value by default

ALTER TABLE hotel CHANGE COLUMN url slug varchar (255) NOT
NULL DEFAULT ";

Rename table

ALTER TABLE worker RENAME TO workers;

Delete table

DROP TABLE table1[,table2, ...]

Delete index

ALTER TABLE DROP INDEX worker_code_idx;

Delete column

Delete the code column from workers table

ALTER TABLE workers DROP COLUMN code;