SQL Reference ยท Data Definition
SQL CREATE SCHEMA and CREATE DATABASE
Reference for CREATE SCHEMA and CREATE DATABASE: how databases and schemas relate in PostgreSQL, MySQL, SQL Server, Oracle and SQLite, with examples.
Databases organize tables into containers, but they do not agree on what to call them. The SQL standard says a catalog contains schemas, and a schema contains tables, views and other objects. CREATE SCHEMA creates one of those namespaces so related tables can be grouped, named schema.table and given their own permissions.
Real products differ: in MySQL a schema and a database are the same thing, Oracle ties a schema to a user, and SQLite has no CREATE SCHEMA at all, only separate database files that you attach.
CREATE SCHEMA sales; CREATE TABLE sales.orders (id INTEGER PRIMARY KEY); SELECT * FROM sales.orders;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
CREATE SCHEMA [IF NOT EXISTS] schema_name [AUTHORIZATION owner]; CREATE DATABASE [IF NOT EXISTS] database_name; DROP SCHEMA [IF EXISTS] schema_name [CASCADE | RESTRICT]; DROP DATABASE [IF EXISTS] database_name;
| Part | Description |
|---|---|
schema_name |
Namespace for tables and other objects. |
AUTHORIZATION owner |
The role that owns the schema (PostgreSQL, SQL Server). |
CASCADE |
PostgreSQL: also drops everything inside the schema. |
Dialect differences
| Dialect | Database vs schema |
|---|---|
| SQLite | A database is a file. No CREATE SCHEMA/CREATE DATABASE; use ATTACH DATABASE to add files, referred to by an alias. |
| PostgreSQL | A server holds databases; each database holds schemas (default public). Queries cannot cross databases. search_path decides which schema is used for unqualified names. |
| MySQL | CREATE SCHEMA is a synonym for CREATE DATABASE. Cross-database queries work: db.table. |
| SQL Server | Instance, then databases, then schemas (default dbo). CREATE SCHEMA must be the first statement in a batch. Three-part names: db.schema.table. |
| Oracle | A schema is the set of objects owned by a user; you create one by creating a user (CREATE USER). CREATE SCHEMA only bundles several creations in one transaction. |
-- PostgreSQL CREATE SCHEMA IF NOT EXISTS sales AUTHORIZATION report_owner; SET search_path TO sales, public; -- MySQL CREATE DATABASE IF NOT EXISTS shop_archive CHARACTER SET utf8mb4;
Usage notes
- Qualify names (
sales.orders) in scripts instead of relying on defaults. - Use schemas for permissions: grant a role access to a whole schema in one statement.
- Separate databases give stronger isolation but usually prevent joins across them (PostgreSQL).
DROP DATABASEdeletes everything and cannot be undone; most servers refuse while you are connected to it.- Creating a database or schema normally needs administrative privileges.