CREATE SCHEMADDL
Buy Me A Coffee

Create a named namespace within a database that groups related tables, views, and other objects. Schemas provide organizational structure and a layer of access control.

Creates a named namespace within a database for grouping related objects. Simplifies object management and allows multiple teams to work in the same database without name collisions. Schema ownership and privileges can be controlled separately.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 5.0 In MySQL, SCHEMA is a synonym for DATABASE — CREATE SCHEMA and CREATE DATABASE are interchangeable. There is no sub-database namespace concept; each schema/database is a fully separate unit. The dot notation (schema.table) is the cross-database reference syntax. This differs from every other engine where schemas are namespaces within a database.
PostgreSQL ✓ Supported 7.3 Schemas are namespaces within a PostgreSQL database (distinct from databases themselves). The default schema is public. search_path controls which schemas are searched when an unqualified name is used. CREATE SCHEMA ... AUTHORIZATION assigns schema ownership. Objects in different schemas can be queried in the same connection; cross-database queries require foreign data wrappers (postgres_fdw).
SQL Server ✓ Supported 2005 Schemas are sub-namespaces within a database (SQL Server 2005 replaced the old owner-based dbo.table model). The default schema is dbo. Each login has a default schema. Permissions can be granted at the schema level, applying to all current and future objects. The four-part name server.database.schema.object can reference objects across databases and linked servers.
Oracle ✗ Not Supported — Oracle does not have a separate SCHEMA object — in Oracle, every user IS a schema. Creating a user (CREATE USER) creates the schema of the same name. Objects are owned by users/schemas, and the two terms are interchangeable in Oracle. There is no CREATE SCHEMA DDL to create a new namespace independently of a user. The CREATE SCHEMA statement exists in Oracle but only as a convenience to create multiple objects in one transaction, not to create the schema itself.
SQLite ✗ Not Supported — SQLite has no schema namespace concept within a database file. Each SQLite database file is a single namespace. The ATTACH DATABASE statement allows attaching multiple database files to one connection, and the attached databases are referenced by alias — this is the closest equivalent to cross-schema access.

Details

The term 'schema' means fundamentally different things across engines. In MySQL, SCHEMA = DATABASE — they are completely synonymous. In PostgreSQL, SQL Server, and the SQL standard, a schema is a namespace within a database (one database can contain many schemas). In Oracle, a schema is inseparable from a user account — you cannot create a schema without creating a user. SQLite has no schemas; ATTACH DATABASE provides a similar multi-namespace capability. The SQL standard search path concept (controlling which schema is default for unqualified names) exists natively in PostgreSQL (search_path) and SQL Server (default schema per login), but not in MySQL or Oracle in the same form.

Standard Syntax

CREATE SCHEMA sales; CREATE TABLE sales.orders ( id INT PRIMARY KEY, amount DECIMAL(10,2) ); SELECT * FROM sales.orders;

Version Support

MySQL: Since 5.0 PostgreSQL: Since 7.3 SQL Server: Since 2005 Oracle: Not supported SQLite: Not supported

Per-Database Syntax & Notes

MySQL

In MySQL, SCHEMA is a synonym for DATABASE — CREATE SCHEMA and CREATE DATABASE are interchangeable. There is no sub-database namespace concept; each schema/database is a fully separate unit. The dot notation (schema.table) is the cross-database reference syntax. This differs from every other engine where schemas are namespaces within a database.

-- CREATE SCHEMA = CREATE DATABASE in MySQL: CREATE SCHEMA sales; -- Equivalent to: CREATE DATABASE sales; -- Use the schema: USE sales; CREATE TABLE orders (id INT PRIMARY KEY, amount DECIMAL(10,2)); -- Cross-database reference: SELECT * FROM sales.orders; -- CREATE SCHEMA with charset: CREATE SCHEMA sales CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

PostgreSQL

Schemas are namespaces within a PostgreSQL database (distinct from databases themselves). The default schema is public. search_path controls which schemas are searched when an unqualified name is used. CREATE SCHEMA ... AUTHORIZATION assigns schema ownership. Objects in different schemas can be queried in the same connection; cross-database queries require foreign data wrappers (postgres_fdw).

-- Create a schema: CREATE SCHEMA sales; CREATE SCHEMA sales AUTHORIZATION alice; -- owned by alice -- Create a table in the schema: CREATE TABLE sales.orders ( id SERIAL PRIMARY KEY, amount NUMERIC(10,2) ); -- Set the search path for the session: SET search_path TO sales, public; -- After setting search_path, unqualified names resolve to sales first: SELECT * FROM orders; -- resolves to sales.orders -- Grant schema usage: GRANT USAGE ON SCHEMA sales TO reporting_role; GRANT SELECT ON ALL TABLES IN SCHEMA sales TO reporting_role;

SQL Server

Schemas are sub-namespaces within a database (SQL Server 2005 replaced the old owner-based dbo.table model). The default schema is dbo. Each login has a default schema. Permissions can be granted at the schema level, applying to all current and future objects. The four-part name server.database.schema.object can reference objects across databases and linked servers.

-- Create a schema: CREATE SCHEMA sales AUTHORIZATION dbo; -- Create table in schema: CREATE TABLE sales.orders ( id INT IDENTITY PRIMARY KEY, amount DECIMAL(10,2) ); -- Transfer an existing table to a different schema: ALTER SCHEMA sales TRANSFER dbo.legacy_orders; -- Grant permissions at schema level: GRANT SELECT ON SCHEMA::sales TO reporting_user; GRANT EXECUTE ON SCHEMA::sales TO api_user; -- Four-part reference: SELECT * FROM myserver.mydb.sales.orders;

Oracle

Oracle does not have a separate SCHEMA object — in Oracle, every user IS a schema. Creating a user (CREATE USER) creates the schema of the same name. Objects are owned by users/schemas, and the two terms are interchangeable in Oracle. There is no CREATE SCHEMA DDL to create a new namespace independently of a user. The CREATE SCHEMA statement exists in Oracle but only as a convenience to create multiple objects in one transaction, not to create the schema itself.

-- In Oracle, schema = user: CREATE USER sales IDENTIFIED BY password DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE TABLE TO sales; -- Log in as the sales user: -- sqlplus sales/password@db CREATE TABLE orders (id NUMBER PRIMARY KEY, amount NUMBER(10,2)); -- This table is now sales.orders -- Access from another user: SELECT * FROM sales.orders; -- Grant access: GRANT SELECT ON sales.orders TO reporting_user;

SQLite

SQLite has no schema namespace concept within a database file. Each SQLite database file is a single namespace. The ATTACH DATABASE statement allows attaching multiple database files to one connection, and the attached databases are referenced by alias — this is the closest equivalent to cross-schema access.

-- No schemas, but ATTACH DATABASE gives a similar namespace: ATTACH DATABASE '/data/sales.db' AS sales; ATTACH DATABASE '/data/hr.db' AS hr; -- Cross-database (cross-schema equivalent) query: SELECT s.id, h.employee_name FROM sales.orders s JOIN hr.employees h ON h.id = s.employee_id; -- The main database is always accessible as 'main': SELECT * FROM main.products; -- List attached databases: PRAGMA database_list;