SQL Create

The CREATE TABLE statement defines a new table in SQL, specifying columns, data types, constraints, and relationships to other tables. Some SQL databases (e.g., PostgreSQL) support table inheritance, which allows a child table to inherit columns and constraints from a parent table using the INHERIT keyword.

---

Basic Syntax

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

---

Example: Creating a Simple Table

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Breakdown:

• id SERIAL PRIMARY KEY: Auto-incrementing unique identifier.
• username VARCHAR(50) UNIQUE NOT NULL: Unique and mandatory username.
• email VARCHAR(100) NOT NULL: Mandatory email.
• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP: Default value set to the current time.

---

Adding Constraints

Common Constraints

Constraint	Description
PRIMARY KEY	Ensures unique row identification.
FOREIGN KEY	Enforces referential integrity.
UNIQUE	Ensures all values in a column are unique.
NOT NULL	Prevents NULL values.
CHECK	Restricts values based on a condition.
DEFAULT	Assigns a default value if none is provided.

Example using constraints:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) CHECK (amount > 0),
    status VARCHAR(20) DEFAULT 'pending'
);

---

Creating Tables with Foreign Keys

To establish relationships, use FOREIGN KEY:

CREATE TABLE orders (
	order_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id) NOT NULL ON DELETE CASCADE,
    amount DECIMAL(10,2) CHECK (amount > 0),
    status VARCHAR(20) DEFAULT 'pending',
)

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) CHECK (amount > 0),
    status VARCHAR(20) DEFAULT 'pending',
    CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Key Notes:

• FOREIGN KEY (user_id) REFERENCES users(id): Enforces relationship with users.
• ON DELETE CASCADE: If a user is deleted, their orders are also deleted.

---

Using Table Inheritance (INHERIT)

In PostgreSQL, tables can inherit columns from a parent table. The child table will have all columns from the parent and can define additional columns.

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

Now, we create a specialized table that inherits from employees:

CREATE TABLE pilots (
    license_number VARCHAR(20) UNIQUE NOT NULL,
    flight_hours INT CHECK (flight_hours >= 0)
) INHERITS (employees);

How It Works:

• The pilots table automatically gets the id, name, and department columns from employees.
• license_number and flight_hours are specific to pilots.
• Queries on employees can return rows from pilots unless explicitly excluded.

---

Key Differences Between Inheritance and Foreign Keys

Feature	Table Inheritance (INHERIT)	Foreign Keys (FOREIGN KEY)
Columns	Child table gets all columns from parent	Child table defines its own columns
Constraints	Parent constraints are inherited	Constraints must be explicitly defined
Querying	Queries on parent table return child rows	Queries must join related tables
Use Case	Useful for hierarchical structures	Used for strict relational integrity