📖 MySQL Modify a Database

ALTER the Structure of a Table

The ALTER keyword can be used to add, remove, or modify columns, as well as change table constraints. These modifications are crucial when adapting a database to new requirements or improving its structure.

Important Consideration: In a SQL script, it's essential to create the table first using CREATE TABLE before applying any modifications with ALTER TABLE. If you attempt to alter a table before it exists, the script will fail. It's common practice, especially in automated scripts, to place ALTER TABLE statements at the end of the script after all tables have been created. This ensures that all tables and their dependencies are fully created before any modifications are applied.

add a column

ALTER TABLE orders
ADD transaction_date DATE;

delete a column

ALTER TABLE orders
DROP COLUMN transaction_date;

modify a column

ALTER TABLE orders
MODIFY transaction_date VARCHAR(10) NOT NULL UNIQUE;

rename a column

ALTER TABLE orders
RENAME COLUMN transaction_date TO order_date;

add a primary key or change table constraints

ALTER TABLE orders
ADD PRIMARY KEY (order_num);

add a foreign key

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

Best Practices for Using ALTER TABLE in Scripts

When writing SQL scripts that include both CREATE TABLE and ALTER TABLE statements, it's best practice to place all ALTER TABLE commands at the end of the script. This ensures that:

  • All tables are created before modifications are applied, avoiding potential errors.
  • Foreign keys and other constraints reference existing tables, preventing dependency issues.

Here's an example of how a script might be organized:

-- Step 1: Create all tables
CREATE TABLE customers (
    customerID INT PRIMARY KEY,
    customerName VARCHAR(100)
);

CREATE TABLE orders (
    orderID INT PRIMARY KEY,
    orderDate DATE,
    customer_id INT
);

-- Step 2: Apply all ALTER TABLE modifications after tables are created
ALTER TABLE orders
ADD transaction_date DATE;

ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customerID);

-- Step 3: Insert data
INSERT INTO customers (customerID, customerName)
VALUES (101, 'John Doe');

INSERT INTO orders (orderID, orderDate, transaction_date, customer_id)
VALUES (1, '2024-08-29', '2024-08-29', 101);

-- Step 4: Create additional indexes
CREATE INDEX idx_order_date_customer
ON orders (orderDate, customer_id);

Add/Modify Indexes

Indexes are essential for optimizing the performance of your database, especially for speeding up searches and joins. By default, primary keys, foreign keys, and unique keys automatically create indexes. However, additional indexes can be created for other columns that are frequently used in search conditions and joins.

create an index

CREATE INDEX idx_transaction_date
ON orders (transaction_date);

composite index on multiple columns

CREATE INDEX idx_order_date_customer
ON orders (order_date, customer_id);

drop an index

DROP INDEX idx_transaction_date
ON orders;