MySQL CREATE TABLE Statement
MySQL CREATE TABLE Statement
The CREATE TABLE statement in MySQL is used to create a new table in a database. Tables are the core structures where data is stored in rows and columns.
🔹 Syntax
-
table_name→ Name of the table to create -
column_name→ Name of each column -
datatype→ Type of data stored in the column (e.g., INT, VARCHAR, DATE) -
constraints→ Optional rules (e.g., PRIMARY KEY, NOT NULL, UNIQUE)
🔹 Common Data Types
| Data Type | Description |
|---|---|
| INT | Integer numbers |
| FLOAT / DOUBLE | Decimal numbers |
| VARCHAR(n) | Variable-length string (max n chars) |
| CHAR(n) | Fixed-length string |
| DATE | Date (YYYY-MM-DD) |
| DATETIME | Date and time |
| BOOLEAN / TINYINT(1) | TRUE/FALSE |
🔹 Common Constraints
| Constraint | Purpose |
|---|---|
| PRIMARY KEY | Uniquely identifies each row |
| AUTO_INCREMENT | Automatically generates a unique value |
| NOT NULL | Column cannot have NULL values |
| UNIQUE | Column must have unique values |
| DEFAULT | Sets a default value for the column |
| FOREIGN KEY | Links column to a primary key in another table |
🔹 Example 1: Simple Table Creation
-
Creates a table
Studentswith 4 columns:id,name,dept,marks -
idis the primary key and auto-incremented -
namecannot be NULL
🔹 Example 2: Table with Foreign Key
-
Students.dept_idreferencesDepartments.dept_id -
Ensures referential integrity between tables
🔹 Key Points
-
CREATE TABLEis used to define the structure of a table. -
Columns should have appropriate data types and constraints.
-
Primary key uniquely identifies rows, foreign key maintains relationships.
-
Table names should be descriptive and unique in the database.
