CREATE SCHEMA

What a Schema Is

A schema is a namespace inside a database that groups related objects — tables, views, functions, sequences — under one name, so sales.orders and hr.orders can coexist without conflict even though both are called orders. Schemas are the standard way to organize a large database and to scope permissions.

sql
-- PostgreSQL / SQL Server / Oracle style
CREATE SCHEMA sales AUTHORIZATION sales_admin;

CREATE TABLE sales.orders (
    id       SERIAL PRIMARY KEY,
    customer VARCHAR(100),
    amount   NUMERIC(10,2)
);

CREATE TABLE hr.orders (          -- a totally different table, no name clash
    id        SERIAL PRIMARY KEY,
    equipment VARCHAR(100)
);

The Critical Dialect Nuance

This is a genuine trap for anyone moving between engines. In PostgreSQL, SQL Server, and Oracle, a schema is a namespace living inside one database — one database can hold many schemas. In MySQL, however, CREATE SCHEMA is a literal, byte-for-byte synonym for CREATE DATABASE; MySQL has no separate "namespace inside a database" concept at all — what MySQL calls a schema is the database. So CREATE SCHEMA sales; in MySQL creates an entirely new database named sales, not a namespace inside your current one. Always know which engine you're on before reading schema-related SQL.

How It Works (Postgres/SQL Server Style)

Every database ships with a default schema (public in PostgreSQL, dbo in SQL Server). Unqualified object references resolve using a search_path (Postgres) or default schema (SQL Server). A worked example — multi-tenant isolation by schema:

sql
CREATE SCHEMA tenant_acme;
CREATE SCHEMA tenant_globex;

CREATE TABLE tenant_acme.users   (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE tenant_globex.users (id SERIAL PRIMARY KEY, name TEXT);

GRANT USAGE ON SCHEMA tenant_acme TO acme_app_role;

Each tenant's application connects with a role whose search_path (or explicit qualification) points only at its own schema, giving logical isolation without spinning up a whole new database per customer.

Edge Cases and Pitfalls

• Object name collisions across different schemas are perfectly fine — that's the whole point.
• Dropping a schema with DROP SCHEMA sales CASCADE; drops every object inside it — just as destructive as DROP DATABASE, but scoped smaller.
• Permissions are commonly granted per-schema (GRANT USAGE ON SCHEMA), which is a common way to sandbox a group of tables from a group of users.
• Forgetting to qualify a table name can silently resolve to the wrong schema if your search_path includes more than one schema.

Key Takeaways / Q&A

Q: Can I have two tables named `orders` in the same database? A: Only if they live in different schemas (Postgres/SQL Server/Oracle). Not possible in MySQL, where "schema" == "database", so there's no intermediate namespace to separate them.

Q: Is CREATE SCHEMA ever destructive on its own? A: No — creating a schema is additive and safe. It's DROP SCHEMA ... CASCADE that is destructive.