MySQL CREATE TABLE

Syntax for Creating a Table

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);

• table_name: The name of your table.

• column1, column2, ...: Names of the table columns.

• datatype: The type of data the column will store (e.g., INT, VARCHAR, TEXT, DATE).

• constraints: Rules applied to columns (e.g., PRIMARY KEY, NOT NULL, AUTO_INCREMENT).

Example: Creating a Users Table

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation:

• id INT AUTO_INCREMENT PRIMARY KEY → Creates a unique id that increments automatically.

• name VARCHAR(100) NOT NULL → Stores the user's name with a maximum of 100 characters and ensures it is not empty.

• email VARCHAR(150) UNIQUE NOT NULL → Stores email addresses, must be unique, and cannot be empty.

• password VARCHAR(255) NOT NULL → Stores hashed passwords.

• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP → Automatically sets the current date and time when a record is created.

Additional Constraints and Features

1. Foreign Key Example

If you want to link another table (e.g., orders) to the users table:

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

• FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE → Ensures that when a user is deleted, all their orders are also deleted.

2. Adding Default Values

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    stock INT NOT NULL DEFAULT 10
);

• DECIMAL(10,2) → Stores prices with two decimal places.

• DEFAULT → Sets default values for price and stock.

3. Enum for Limited Options

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    role ENUM('Admin', 'Manager', 'Employee') NOT NULL
);

• ENUM('Admin', 'Manager', 'Employee') → Restricts the role column to only accept these values.

Checking Table Structure

To see the structure of a table:

DESCRIBE users;

Modifying an Existing Table

1. Add a New Column

ALTER TABLE users ADD COLUMN phone VARCHAR(20) NULL;

2. Modify an Existing Column

ALTER TABLE users MODIFY COLUMN email VARCHAR(200) NOT NULL;

3. Remove a Column

ALTER TABLE users DROP COLUMN phone;

Deleting a Table

To delete a table permanently:

DROP TABLE users;