Creating SQL Tables and Schemas
1. Creating Tables
| Clause | Purpose |
|---|---|
CREATE TABLE | Define new table |
IF NOT EXISTS | Skip if already present |
AS SELECT (CTAS) | Copy structure + data from query |
LIKE other_table | Clone structure only |
INHERITS (Postgres) | Table inheritance |
PARTITION BY | Declarative partitioning |
Example: Comprehensive CREATE TABLE
CREATE TABLE IF NOT EXISTS products (
id BIGSERIAL PRIMARY KEY,
sku VARCHAR(50) NOT NULL UNIQUE,
name TEXT NOT NULL,
price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
category_id BIGINT REFERENCES categories(id) ON DELETE SET NULL,
attrs JSONB DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);2. Defining Column Data Types
| Category | Postgres | MySQL |
|---|---|---|
| Integer | SMALLINT, INT, BIGINT, SERIAL | TINYINT, INT, BIGINT, AUTO_INCREMENT |
| Decimal | NUMERIC(p,s), REAL, DOUBLE PRECISION | DECIMAL(p,s), FLOAT, DOUBLE |
| String | VARCHAR(n), TEXT, CHAR(n) | VARCHAR(n), TEXT, CHAR(n) |
| Date/Time | DATE, TIME, TIMESTAMPTZ, INTERVAL | DATE, TIME, DATETIME, TIMESTAMP |
| Boolean | BOOLEAN | TINYINT(1) / BOOLEAN |
| JSON | JSONB (binary), JSON (text) | JSON |
| UUID | UUID | CHAR(36) / BINARY(16) |
| Binary | BYTEA | BLOB, VARBINARY |
| Array | type[] | JSON array |
3. Setting Column Constraints
| Constraint | Syntax | Effect |
|---|---|---|
| NOT NULL | NOT NULL | Column must have a value |
| DEFAULT | DEFAULT expr | Auto-fill on insert |
| UNIQUE | UNIQUE | No duplicates (NULLs allowed) |
| PRIMARY KEY | PRIMARY KEY | UNIQUE + NOT NULL |
| CHECK | CHECK (expr) | Custom validation rule |
| FOREIGN KEY | REFERENCES t(c) | Referential integrity |
| GENERATED | GENERATED ALWAYS AS | Computed column |
4. Defining Primary Keys
| Approach | Pros | Cons |
|---|---|---|
| BIGSERIAL / AUTO_INCREMENT | Compact, sequential | Reveals row counts, hard across shards |
| UUID v4 | Globally unique, no central authority | Random → index bloat |
| UUID v7 NEW | Time-ordered, index-friendly | Newer standard, lib support varies |
| Snowflake ID | Sortable, distributed | Needs ID service |
| Composite Key | Natural for junction tables | Larger FK references |
5. Creating Foreign Keys
| Action | Behavior on Parent Delete/Update |
|---|---|
| NO ACTION (default) | Error if child rows exist (deferred check) |
| RESTRICT | Error immediately |
| CASCADE | Delete/update child rows |
| SET NULL | Set FK to NULL (column must allow NULL) |
| SET DEFAULT | Set FK to DEFAULT value |
Example: FK with ON DELETE CASCADE
ALTER TABLE order_items
ADD CONSTRAINT fk_order
FOREIGN KEY (order_id) REFERENCES orders(id)
ON DELETE CASCADE
ON UPDATE RESTRICT;6. Adding Check Constraints
| Pattern | Example |
|---|---|
| Range | CHECK (age BETWEEN 0 AND 150) |
| Enum-like | CHECK (status IN ('open','closed','pending')) |
| Regex (Postgres) | CHECK (email ~ '^[^@]+@[^@]+\.[^@]+$') |
| Cross-column | CHECK (end_date > start_date) |
| JSON validation | CHECK (jsonb_typeof(meta->'tags') = 'array') |
7. Creating Schemas and Namespaces
| Operation | SQL |
|---|---|
| Create schema | CREATE SCHEMA sales; |
| Drop schema | DROP SCHEMA sales CASCADE; |
| Qualify name | sales.invoices |
| Set search path | SET search_path TO sales, public; |
| Grant usage | GRANT USAGE ON SCHEMA sales TO role; |
8. Altering Tables
| Operation | SQL |
|---|---|
| Add column | ALTER TABLE t ADD COLUMN c TYPE [DEFAULT v]; |
| Drop column | ALTER TABLE t DROP COLUMN c; |
| Rename column | ALTER TABLE t RENAME COLUMN a TO b; |
| Change type | ALTER TABLE t ALTER COLUMN c TYPE NEWTYPE USING expr; |
| Add constraint | ALTER TABLE t ADD CONSTRAINT name CHECK (...); |
| Drop constraint | ALTER TABLE t DROP CONSTRAINT name; |
| Rename table | ALTER TABLE t RENAME TO new_t; |
Warning: In Postgres < 11, adding a NOT NULL column with default rewrites the whole table. PG 11+ avoids the rewrite for constant defaults.
9. Dropping Tables
| Form | Effect |
|---|---|
DROP TABLE t; | Error if dependencies exist |
DROP TABLE IF EXISTS t; | No error if missing |
DROP TABLE t CASCADE; | Also drops dependent FKs/views |
TRUNCATE TABLE t; | Empty rows but keep schema (faster than DELETE) |
10. Using Temporary Tables
| Scope | Lifetime |
|---|---|
| SESSION (default) | Until disconnect |
| TRANSACTION | Until COMMIT/ROLLBACK (ON COMMIT DROP) |
| LOCAL | Visible only to current session |
| GLOBAL (SQL Server) | Visible to all sessions, dropped when last session ends |