MariaDB Tutorial / Change Table Structure Safely with ALTER TABLE

ALTER TABLE changes columns, types and constraints in an existing table. Check that current rows satisfy the new rules, then separate adding a column, filling existing data and enforcing constraints so failures and information loss remain visible.

Preserve the current definition and recovery path

A schema change is more than renaming a label. Narrower types, NOT NULL and UNIQUE can fail against existing rows or transform values. Preserve SHOW CREATE TABLE, suitable data backups and knowledge of dependent queries, then reproduce the change in a separate environment first. Define the proposed conditions using Define Input Rules with NULL, DEFAULT, NOT NULL, and CHECK, and prepare separate recovery evidence for potentially lossy changes as in Back Up an InnoDB Database with mariadb-dump.

Check the MariaDB documentation for the syntax and conditions used here: alter table.

Prepare existing products before adding a code

Connect to the learning server as course_user and select shopcourse. The command-line and UTF-8 SQL-file guide explains how to save the code as a UTF-8 file and execute it with SOURCE C:/mariadb-lab/lesson.sql;, replacing the path with the actual file. Run SQL inside the MariaDB client, rather than at the operating-system prompt.

Check SELECT VERSION(), DATABASE(); and SHOW TABLES; before creating products_38_lab. These names belong only to this exercise. If they exist, inspect their definitions and rows first; remove them only when you have established that they are disposable exercise objects. Do not replace existing application tables.

USE shopcourse;
CREATE TABLE products_38_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p38_price CHECK (price >= 0),
  CONSTRAINT ck_p38_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_38_lab (product_id,product_name,category,price,stock,note) VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

The SQL retains the original Korean sample strings to exercise UTF-8 consistently. Product identifiers 1–6 represent a notebook, pen, mug, lamp, folder and cable; their category strings mean stationery, household and electronics. Enter the string literals as shown, use the identifiers when comparing results, and save the file with UTF-8 encoding.

The exercise adds sku to all existing products. Start by allowing NULL so values can be filled before they become mandatory. The generated code convention is only for this fixture; actual length, stability and uniqueness scope need business definitions.

Add a nullable column, fill it and reconcile counts

SHOW CREATE TABLE products_38_lab;
ALTER TABLE products_38_lab ADD COLUMN sku VARCHAR(20) NULL;
UPDATE products_38_lab SET sku=CONCAT('P',LPAD(product_id,3,'0'))
WHERE sku IS NULL;
SELECT COUNT(*) AS rows_total,COUNT(sku) AS known_sku,
 COUNT(DISTINCT sku) AS unique_sku FROM products_38_lab;

The new column initially contains NULL for six existing rows. After the UPDATE produces P001 through P006, rows_total, known_sku and unique_sku must all be six. If any values remain absent or duplicated, stop before imposing the final constraints.

Do not assume ALTER and the following UPDATE form one rollbackable transaction. DDL has separate commit boundaries. Record which stage succeeded so an interrupted migration can be resumed or restored deliberately.

Apply constraints and test a rejected change separately

ALTER TABLE products_38_lab
 MODIFY COLUMN sku VARCHAR(20) NOT NULL,
 ADD CONSTRAINT uq_p38_sku UNIQUE(sku),
 ADD CONSTRAINT ck_p38_name CHECK(CHAR_LENGTH(product_name)>0);
SHOW CREATE TABLE products_38_lab;
SELECT product_id,product_name,sku FROM products_38_lab ORDER BY product_id;

sku becomes required and unique, and the product-name check requires a positive character count. When using MODIFY, compare and restate the type, length, NULL rule, defaults and other attributes that must remain; omitted attributes do not universally preserve themselves.

Run the following deliberate failure separately. It tries to give product 2 the code P001 already held by product 1. Expect a UNIQUE error and requery product 2 to confirm its original P002 remains. Also verify the intended empty-name check without disabling enforcement.

UPDATE products_38_lab SET sku='P001' WHERE product_id=2;

Review locking, online options and deployment cost

A change can require a copy or locks depending on the version, engine, operation and table size. ALGORITHM and LOCK options do not make every alteration interruption-free. Check supported combinations, failure behavior and application wait effects on the actual version.

A long transaction can retain metadata locks and delay ALTER. A tiny fixture’s duration does not predict production duration. Include data and index volume, disk use, recovery time and any maintenance window or compatible staged deployment in the plan.

Distinguish removing a new column from restoring data

Dropping a newly added column destroys values accumulated in it; that is not necessarily a restoration of the original data state. Potentially lossy changes need prior backups and a verified recovery procedure. Here, compare the original six names, prices and stocks before removing only the disposable practice table.

Before repeating the exercise, inspect the selected database, actual table definitions and stored rows, and back up anything that must be kept. Drop only this article’s disposable practice objects, with child tables before their referenced parents. DROP TABLE removes both definitions and rows and cannot be undone by an ordinary ROLLBACK; keep cleanup separate from transaction experiments.

DROP TABLE shopcourse.products_38_lab;