Documentation > SQL > Relational Databases > Schemas

SQL Schemas

What is a Schema?

In a relational database, a schema is a collection of database objects. It defines the structure of the database, including tables, views, stored procedures, functions, and other objects. Schemas help organize database objects logically and can be used to manage permissions and security.

Think of a schema as a namespace or a blueprint for a part of your database. It allows you to group related objects together, making the database easier to manage and understand, especially in large or complex environments.

Key Concepts

  • Logical Grouping: Schemas provide a way to group related database objects, such as all objects belonging to a specific application or department.
  • Namespace: Objects within different schemas can have the same name without conflict, as the schema name acts as a qualifier.
  • Security and Permissions: You can grant or deny specific permissions to users or roles on individual schemas, controlling access to the objects within them.
  • Ownership: Each schema has an owner, typically a database user or role, who has full control over the objects in that schema.

Creating and Managing Schemas

Schemas are typically created using Data Definition Language (DDL) statements. The exact syntax may vary slightly depending on the specific SQL database system (e.g., SQL Server, PostgreSQL, MySQL).

Creating a Schema (Example using SQL Server syntax):

CREATE SCHEMA Sales; GO

Assigning Objects to a Schema:

When creating objects like tables, you can specify the schema they belong to:

CREATE TABLE Sales.Customers ( CustomerID INT PRIMARY KEY, CustomerName VARCHAR(255), ContactName VARCHAR(255) ); GO

Accessing Objects in a Schema:

To reference objects in a schema, you use the schema-qualified name (e.g., Sales.Customers).

SELECT CustomerName FROM Sales.Customers WHERE CustomerID = 1; GO

Default Schema:

Each database user is typically assigned a default schema. If no schema is specified when creating an object, it is created in the user's default schema.

Schema Ownership and Permissions:

You can alter schema properties, change ownership, and manage permissions using `ALTER SCHEMA` and `GRANT`/`DENY` statements.

Common Schemas

Many database systems come with built-in schemas for managing system objects, security, and common functionalities:

  • dbo (Database Owner): In SQL Server, this is the default schema for objects created without a specified schema.
  • sys: Contains system catalog views and information schemas.
  • INFORMATION_SCHEMA: A standard SQL schema that provides views for database metadata (tables, columns, etc.).
  • pg_catalog (PostgreSQL): Contains system tables and functions.

Best Practices

  • Use schemas to logically separate objects related to different applications, modules, or environments (e.g., Development, Staging, Production).
  • Apply the principle of least privilege when assigning permissions to schemas.
  • Establish a clear naming convention for your schemas.
  • Avoid placing all objects in the default schema (like dbo) in large databases to maintain organization.