CREATE TABLE - MySQL

CREATE TABLE

Overview

The CREATE TABLE command in MySQL creates a new table in a database. It defines the structure of the table by specifying its columns, data types, constraints, and other properties.

Syntax

CREATE TABLE table_name (
  column_name data_type [NOT NULL] [DEFAULT default_value] [PRIMARY KEY],
  ...
);

Options/Flags

• NOT NULL: Ensures that the column cannot contain null values (except for columns with a DEFAULT value).
• DEFAULT default_value: Specifies the default value for the column if no value is provided.
• PRIMARY KEY: Designates a unique column (or columns) that can be used to identify rows uniquely within the table.

Examples

Simple Example:

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL
);

Complex Example with Constraints:

CREATE TABLE orders (
  order_id INT NOT NULL AUTO_INCREMENT,
  item_id INT NOT NULL,
  quantity INT NOT NULL,
  customer_id INT NOT NULL,
  PRIMARY KEY (order_id),
  FOREIGN KEY (item_id) REFERENCES items(item_id),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Common Issues

• Duplicate Column Names: Column names must be unique within a table.
• Missing PRIMARY KEY: Every table should have at least one PRIMARY KEY.
• Incorrect Data Types: Ensure that the data types selected for columns match the intended data they will contain.

Integration

• Can be used with ALTER TABLE to modify existing tables.
• Used in conjunction with INSERT, UPDATE, and DELETE to manage data in the table.
• Forms the foundation for relational database modeling.

Related Commands

• DROP TABLE – Removes a table from the database.
• ALTER TABLE – Modifies the structure of an existing table.
• SELECT – Retrieves data from a table.
• INSERT – Adds new rows to a table.
• UPDATE – Modifies existing rows in a table.
• DELETE – Removes rows from a table.