Creating Tables and Constraints

Tables are the fundamental building blocks of a MySQL database. Constraints enforce data integrity directly at the database level, preventing invalid data from ever being stored.

CREATE TABLE Syntax

CREATE TABLE users (
  id         INT UNSIGNED     NOT NULL AUTO_INCREMENT,
  email      VARCHAR(255)     NOT NULL,
  username   VARCHAR(50)      NOT NULL,
  created_at TIMESTAMP        DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

AUTO_INCREMENT automatically generates a unique incrementing integer for each new row, making it ideal for surrogate primary keys. ENGINE=InnoDB selects the transactional storage engine and is the default in MySQL 8.

Primary Keys

A primary key uniquely identifies every row in a table. MySQL creates a clustered index on the primary key, meaning the rows are physically stored in primary key order. This makes primary key lookups extremely fast.

PRIMARY KEY (id)

Composite primary keys are supported when no single column is unique on its own:

PRIMARY KEY (course_id, user_id)

Foreign Keys

Foreign keys link rows in one table to rows in another, enforcing referential integrity:

CREATE TABLE posts (
  id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    INT UNSIGNED NOT NULL,
  title      VARCHAR(255) NOT NULL,
  body       TEXT,
  PRIMARY KEY (id),
  CONSTRAINT fk_posts_user
    FOREIGN KEY (user_id) REFERENCES users (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ON DELETE CASCADE automatically deletes child rows when the parent row is deleted. Other options include SET NULL and RESTRICT.

UNIQUE and NOT NULL Constraints

email VARCHAR(255) NOT NULL UNIQUE

NOT NULL prevents storing a null value in the column. UNIQUE ensures no two rows share the same value and automatically creates a unique index.

CHECK Constraints (MySQL 8.0.16+)

CHECK (price > 0),
CHECK (status IN ('active', 'inactive', 'banned'))

Modifying Tables

After a table is created you can alter it without dropping and recreating it:

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;

Use DROP TABLE to permanently remove a table and all its data:

DROP TABLE IF EXISTS posts;

Well-designed tables with proper constraints catch data errors at the source, keeping your application logic simpler and your data more reliable.