SQL CREATE / ALTER / DROP SCHEMA

Last update on March 27 2024 11:26:37 (UTC/GMT +8 hours)

Database Schema

A schema is a logical database object holder. A database schema of a database system is its structure described in a formal language supported by the database management system. The formal definition of a database schema is a set of formulas (sentences) called integrity constraints imposed on a database. These integrity constraints ensure compatibility between parts of the schema. All constraints are expressible in the same language.
Creating schemas can be useful when objects have circular references, that is when we need to create two tables each with a foreign key referencing the other table. Different implementations treat schemas in slightly different ways.

Syntax:

CREATE SCHEMA [schema_name] [AUTHORIZATION owner_name]
[DEFAULT CHARACTER SET char_set_name]
[PATH schema_name[, ...]]
[ ANSI CREATE statements [...] ]
[ ANSI GRANT statements [...] ];

Parameter:

Name	Description
schema_name	The name of a schema to be created. If this is omitted, the user_name is used as the schema name.
AUTHORIZATION owner_name	Identifies the user who is the owner of the schema. If not mentioned the current user is set as the owner.
DEFAULT CHARACTER SET char_set_name	Specify the default character set, used for all objects created in the schema.
PATH schema_name[, ...]	An optional file path and file name.
ANSI CREATE statements [...]	Contains one or more CREATE statements.
ANSI GRANT statements [...]	Contains one or more GRANT statements.

Examples :

Example-1: As a user with authority, create a schema called STUDENT with the user STUDENT as the owner.

SQL Code:

CREATE SCHEMA STUDENT AUTHORIZATION STUDENT

Example-2: Create a schema that has an student details table. Give authority on the table to user DAVID.

SQL Code:

-- Creating a new schema named INVENTRY
CREATE SCHEMA INVENTRY;

-- Creating a table named PART within the INVENTRY schema
-- The table has three columns: IDNO (SMALLINT), SNAME (VARCHAR with a maximum length of 40 characters), and CLASS (INTEGER)
CREATE TABLE PART (
    IDNO   SMALLINT NOT NULL,
    SNAME  VARCHAR(40),
    CLASS  INTEGER
);

-- Granting all privileges on the PART table to the user DAVID
GRANT ALL ON PART TO DAVID;

Explanation:

In the above example –

 
CREATE SCHEMA INVENTRY;

The above line creates a new schema (a logical container for database objects) named "INVENTRY." Schemas are used to organize database objects like tables, views, etc.

CREATE TABLE PART (
    IDNO   SMALLINT NOT NULL,
    SNAME  VARCHAR(40),
    CLASS  INTEGER
);

This section creates a table named "PART" within the "INVENTRY" schema. The table has three columns:

IDNO with a data type of SMALLINT that cannot contain NULL values (NOT NULL constraint).
SNAME with a data type of VARCHAR(40) which can store variable-length character strings of up to 40 characters.
CLASS with a data type of INTEGER.

GRANT ALL ON PART TO DAVID;

The above line grants all permissions (such as SELECT, INSERT, UPDATE, DELETE) on the "PART" table to a user or role named "DAVID." The user "DAVID" will now have full access to the "PART" table in the "INVENTRY" schema.

Create schema in MySQL [8.2]

In MySQL, CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

Explanation:

This SQL syntax is used to create a new database or schema in a database management system. Below is an explanation of the syntax:

CREATE DATABASE or CREATE SCHEMA: This part of the syntax indicates whether a new database or schema is being created. In most database systems, the terms "database" and "schema" are used interchangeably, but some systems differentiate between them.
IF NOT EXISTS (optional): This clause is used to specify that the database or schema should be created only if it does not already exist. If the database or schema with the specified name already exists, the command will not produce an error.
db_name: This is the name of the database or schema that is being created. It should be a valid identifier and should not conflict with any existing databases or schemas.
create_specification (optional): This part of the syntax allows for specifying additional options for creating the database or schema. Currently, two options are supported:

DEFAULT CHARACTER SET [=] charset_name: This option specifies the default character set to be used for storing character data in the database or schema. The charset_name parameter specifies the name of the character set. Character sets define the encoding of characters used in the database.
DEFAULT COLLATE [=] collation_name: This option specifies the default collation to be used for comparing and sorting character data in the database or schema. The collation_name parameter specifies the name of the collation. Collations define the rules for comparing and sorting characters based on their encoding and language-specific rules.

Create schema in PostgreSQL 16.1

CREATE SCHEMA enters a new schema into the current database. The schema name must be distinct from the name of any existing schema in the current database.

Syntax:

CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ]
CREATE SCHEMA IF NOT EXISTS schema_name [ AUTHORIZATION user_name ]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name

Explanation:

In PostgreSQL, the CREATE SCHEMA statement is used to create a new schema within the current database.
A schema is a collection of database objects (such as tables, views, functions) that allows you to logically organize and manage your database objects.
The different variations of the CREATE SCHEMA statement allow for various scenarios:

CREATE SCHEMA schema_name: Creates a new schema with the specified name.
CREATE SCHEMA AUTHORIZATION user_name: Creates a new schema owned by the specified user.
CREATE SCHEMA IF NOT EXISTS schema_name: Creates a new schema with the specified name only if it does not already exist.
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name: Creates a new schema owned by the specified user only if it does not already exist.

Optionally, you can specify the user who will own the schema using the AUTHORIZATION clause.
Additionally, you can include schema elements such as tables, views, or other database objects within the CREATE SCHEMA statement. These elements will be created within the newly created schema.

Create schema in Oracle 19c

Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.
To execute a CREATE SCHEMA statement, Oracle Database executes each included statement. If all statements execute successfully, then the database commits the transaction. If any statement results in an error, then the database rolls back all the statements.

The CREATE SCHEMA statement can include CREATE TABLE, CREATE VIEW, and GRANT statements. To issue a CREATE SCHEMA statement, you must have the privileges necessary to issue the included statements.

Syntax:

CREATE SCHEMA AUTHORIZATION schema
   { create_table_statement
   | create_view_statement
   | grant_statement
   }...;

Explanation:

CREATE SCHEMA: This keyword initiates the creation of a schema. In Oracle SQL, a schema is a logical container for database objects such as tables, views, indexes, etc. It does not necessarily correspond to a user account; rather, it's a namespace that contains database objects.
AUTHORIZATION schema: This clause specifies the name of the user who will own the schema being created. The user specified in this clause becomes the schema owner and has full control over the objects within the schema.
{ create_table_statement | create_view_statement | grant_statement }: These are the statements or commands that define the structure and permissions of the objects within the schema. Multiple statements can be included within the CREATE SCHEMA block to create tables, views, or grant permissions to other users.

create_table_statement: This statement is used to create tables within the schema. It defines the structure of the tables, including column names, data types, constraints, etc.
create_view_statement: This statement is used to create views within the schema. Views are virtual tables that present data from one or more underlying tables or views in a structured format.
grant_statement: This statement is used to grant privileges or permissions on the objects within the schema to other users or roles. It specifies the type of access (e.g., SELECT, INSERT, UPDATE, DELETE) that is granted to specific users or roles.

Create schema in SQL Server 2022

Creates a schema in the current database. The CREATE SCHEMA transaction can also create tables and views within the new schema, and set GRANT, DENY, or REVOKE permissions on those objects.

Syntax:

The following statement creates a database and fully specifies each argument :

CREATE SCHEMA schema_name_clause [ <schema_element> [ ...n ] ]

<schema_name_clause> ::=
    {
    schema_name
    | AUTHORIZATION owner_name
    | schema_name AUTHORIZATION owner_name
    }

<schema_element> ::= 
    { 
        table_definition | view_definition | grant_statement | 
        revoke_statement | deny_statement 
    }

Explanation:

This SQL Server code snippet outlines the syntax for creating a schema and defining its elements.
CREATE SCHEMA schema_name_clause: This statement initiates the creation of a schema with the specified name clause.
<schema_name_clause>: This section outlines the possible clauses that can be part of the schema creation:

schema_name: Specifies the name of the schema being created.
AUTHORIZATION owner_name: Assigns ownership of the schema to the specified owner.
schema_name AUTHORIZATION owner_name: Combines both schema name and authorization clauses.

: This section specifies the elements that can be included within the schema:

table_definition: Defines the structure of tables within the schema, including column names, data types, constraints, etc.
view_definition: Specifies the definition of views within the schema. Views are virtual tables that present data from underlying tables or views.
grant_statement: Grants permissions on schema objects to users or roles.
revoke_statement: Revokes previously granted permissions.
deny_statement: Explicitly denies permissions on schema objects.
These elements allow for the definition of various schema components, including tables, views, and permissions.

Alter Schema

The ALTER SCHEMA statement is used to rename a schema or to specify a new owner, the new owner must be a pre-existing user on the database

Syntax:

ALTER SCHEMA schema_name [RENAME TO new_schema_name] [OWNER TO new_user_name]

Parameter:

Name	Description
schema_name	The name of an existing schema.
new_schema_name	The new name of the schema.
new_owner	The new owner of the schema.

Alter Schema in MySQL [8.2]

In MySQL, CREATE SCHEMA is a synonym for CREATE DATABASE.

Syntax:

ALTER {DATABASE | SCHEMA} [db_name]
    alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
    UPGRADE DATA DIRECTORY NAME

alter_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

In MySQL, ALTER SCHEMA is a synonym for ALTER DATABASE. ALTER DATABASE enables you to change the overall characteristics of a database. These characteristics are stored in the db.opt file in the database directory. To use ALTER DATABASE, you need the ALTER privilege on the database.

Explanation:

This MySQL syntax is used to alter a database or schema, modifying its characteristics or upgrading its data directory name.

ALTER DATABASE or ALTER SCHEMA: This part of the syntax specifies whether you are altering a database or a schema. In MySQL, databases and schemas are often used interchangeably.
[db_name]: This optional part of the syntax specifies the name of the database or schema that you want to alter.
alter_specification: This part of the syntax specifies the alterations you want to make to the database or schema. It includes:

[DEFAULT] CHARACTER SET [=] charset_name: This clause specifies the default character set to be used for storing character data in the database or schema. The charset_name parameter specifies the name of the character set. Character sets define the encoding of characters used in the database.
[DEFAULT] COLLATE [=] collation_name: This clause specifies the default collation to be used for comparing and sorting character data in the database or schema. The collation_name parameter specifies the name of the collation. Collations define the rules for comparing and sorting characters based on their encoding and language-specific rules.

UPGRADE DATA DIRECTORY NAME: This clause is used to upgrade the data directory name associated with the database or schema specified by db_name. It changes the name of the directory where the data files for the database or schema are stored. This operation is useful when you need to rename the directory containing the data files for the database or schema.
Alter schema in PostgreSQL 16.1

Description ALTER SCHEMA changes the definition of a schema. The user must own the schema to use ALTER SCHEMA. To rename a schema you must also have the CREATE privilege for the database. To alter the owner, you must also be a direct or indirect member of the new owning role, and you must have the CREATE privilege for the database.

Syntax:
```
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner
```
Explanation:

In PostgreSQL 16.1, the ALTER SCHEMA statement is used to modify existing schemas. Below is an explanation of the provided code snippets:
- ALTER SCHEMA name RENAME TO new_name:
- ALTER SCHEMA name OWNER TO new_owner:
Alter schema in SQL Server 2022

ALTER SCHEMA can only be used to move securable between schemas in the same database. Users and schemas are completely separate.

Syntax:
```
ALTER SCHEMA schema_name 
   TRANSFER [ <entity_type> :: ] securable_name 
[;]

<entity_type> ::=
    {
    Object | Type | XML Schema Collection
    }
```
Explanation:

In SQL Server, the ALTER SCHEMA statement with the TRANSFER clause is used to transfer ownership of a securable object from one schema to another.
- ALTER SCHEMA schema_name: This part of the statement specifies the schema from which the ownership of the securable object will be transferred.
- TRANSFER: This keyword indicates that ownership of the specified securable object will be transferred.
- [ :: ]: This is an optional clause that specifies the type of the securable object. It allows you to transfer ownership of different types of objects within the schema. The available entity types are:
- securable_name: This is the name of the securable object whose ownership will be transferred. It can be prefixed with the entity type if specified.
- The statement allows for transferring ownership of securable objects between schemas, providing flexibility in managing schema ownership and access control in the database.
- The ALTER SCHEMA statement with TRANSFER clause is commonly used when reorganizing database schemas, consolidating objects under a different schema, or when ownership needs to be changed for security or organizational reasons.
Drop Schema

Destroy a schema.

Syntax:
```
DROP SCHEMA <schema name>
```
Drop Schema in MySQL [8.2]

DROP DATABASE drops all tables in the database and deletes the database. DROP SCHEMA is a synonym for DROP DATABASE.

Syntax:
```
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
```
Explanation:
- DROP DATABASE or DROP SCHEMA: This part of the syntax specifies whether you are dropping a database or a schema. In MySQL, databases and schemas are often used interchangeably.
- [IF EXISTS]: This clause is optional and allows you to specify that the database or schema should be dropped only if it exists. If the database or schema does not exist and this clause is present, the DROP statement will not produce an error.
- db_name: This is the name of the database or schema that you want to drop. It should be a valid identifier and must not contain any spaces or special characters.
- Caution should be exercised when using the DROP statement, as it permanently deletes the database or schema and its contents. It is recommended to use the IF EXISTS clause to prevent errors if the database or schema does not exist, especially in scripts or automated processes.
Drop schema in PostgreSQL 16.1

DROP SCHEMA removes schemas from the database. A schema can only be dropped by its owner or a superuser.

Syntax:
```
DROP SCHEMA [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
```
Explanation:
- DROP SCHEMA: This is the beginning of the statement, indicating that you want to drop one or more schemas from the database.
- [ IF EXISTS ]: This clause is optional. If specified, it ensures that the statement does not produce an error if the specified schemas do not exist in the database.
- name [, ...]: This part specifies the name or names of the schemas that you want to drop. You can specify multiple schema names separated by commas.
- [ CASCADE | RESTRICT ]: These are optional clauses specifying the behavior of the drop operation when there are dependent objects within the schema(s) being dropped:
- The statement allows you to delete one or more schemas from the PostgreSQL database. Dropping a schema removes all objects contained within it, including tables, views, functions, etc. However, it's important to note that dropping a schema does not delete the data stored in the tables; it only removes the schema structure and associated objects.
- The optional CASCADE and RESTRICT clauses provide control over how the drop operation behaves when there are dependent objects within the schema(s) being dropped, allowing you to customize the behavior according to your requirements.
Drop schema in SQL Server 2022

Conditionally drops the schema only if it already exists.

Syntax:
```
DROP SCHEMA  [ IF EXISTS ] schema_name
```
Explanation:
- DROP SCHEMA: This is the beginning of the statement, indicating that you want to drop a schema from the database.
- [ IF EXISTS ]: This clause is optional. If specified, it ensures that the statement does not produce an error if the specified schema does not exist in the database. If the schema exists, it will be dropped. If it doesn't exist, no action will be taken.
- schema_name: This specifies the name of the schema that you want to drop from the database. It should be a valid schema name in the database.
- The statement allows you to delete a schema from the SQL Server database. Dropping a schema removes the schema and all objects contained within it, such as tables, views, stored procedures, and functions.
- Caution should be exercised when using the DROP SCHEMA statement, as it permanently deletes the schema and its contents. It is recommended to use the IF EXISTS clause to prevent errors if the schema does not exist, especially in scripts or automated processes.
Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

Previous: The Components of a Table
Next: Create/Alter Database

SQL CREATE / ALTER / DROP SCHEMA

Database Schema

Create schema in MySQL [8.2]

Create schema in PostgreSQL 16.1

Create schema in Oracle 19c

Create schema in SQL Server 2022

Alter Schema

Alter Schema in MySQL [8.2]

Alter schema in PostgreSQL 16.1

Alter schema in SQL Server 2022

Drop Schema

Drop Schema in MySQL [8.2]

Drop schema in PostgreSQL 16.1

Drop schema in SQL Server 2022