CodeCapsule

SQL CREATE TABLE statement

by

Here is a clear, complete, and practical guide to the SQL CREATE TABLE statement, with examples for MySQL, PostgreSQL, SQL Server, and Oracle.

✅ What CREATE TABLE Does

CREATE TABLE creates a new table in a database with defined:

  • Columns

  • Data types

  • Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.)

  • Default values

🔹 Basic ANSI SQL Syntax

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

📌 Simple Example

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10,2),
hire_date DATE
);

🔥 Example With Constraints

CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

🔗 Example With FOREIGN KEY

CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
total DECIMAL(10,2),
order_date DATE,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);

🟦 MySQL — CREATE TABLE Example

CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
in_stock BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

🟪 PostgreSQL — CREATE TABLE Example

CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
full_name TEXT NOT NULL,
email TEXT UNIQUE,
signup_date TIMESTAMP DEFAULT NOW()
);

🟧 SQL Server — CREATE TABLE Example

CREATE TABLE invoices (
invoice_id INT IDENTITY(1,1) PRIMARY KEY,
amount DECIMAL(12,2) NOT NULL,
paid BIT DEFAULT 0,
created_at DATETIME DEFAULT GETDATE()
);

🟨 Oracle — CREATE TABLE Example

CREATE TABLE employees (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
salary NUMBER(10,2),
hire_date DATE DEFAULT SYSDATE
);

🔥 Add a CHECK Constraint

CREATE TABLE accounts (
id INT PRIMARY KEY,
balance DECIMAL(10,2) CHECK (balance >= 0)
);

🧩 Common SQL Data Types

Type Description
INT / BIGINT Whole numbers
VARCHAR(n) Text up to n chars
TEXT Long text
DATE Date only
TIMESTAMP / DATETIME Date + time
DECIMAL(p,s) Precise numbers (money)
BOOLEAN True/false
SERIAL (PostgreSQL) Auto-increment integer
AUTO_INCREMENT (MySQL) Auto-increment integer
IDENTITY (SQL Server) Auto-increment integer

🧠 Best Practices

✔ Always define a PRIMARY KEY
✔ Use meaningful column names
✔ Use appropriate data types (especially for money: DECIMAL)
✔ Use NOT NULL where required
✔ Add indexes for frequent search columns
✔ Use foreign keys to maintain data integrity
✔ Use snake_case or lower_case naming conventions
✔ Avoid unnecessary large text fields (TEXT, LONGTEXT)

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *