SQL CREATE / ALTER / DROP SCHEMA

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:

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:

CREATE SCHEMA INVENTRY

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

     GRANT ALL ON PART TO DAVID
	 
	 

Create schema in MySQL [5.7]

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

Create schema in PostgreSQL 9.3.13

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

Create schema in Oracle 11g

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
   }...;

Create schema in SQL Server 2014

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 
    }

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:

Alter Schema in MySQL [5.7]

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.

Alter schema in PostgreSQL 9.3.13

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

Alter schema in SQL Server 2014

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
    }

Drop Schema

Destroy a schema.

Syntax:

DROP SCHEMA <schema name>

Drop Schema in MySQL [5.7]

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

Drop schema in PostgreSQL 9.3.13

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 ]

Drop schema in SQL Server 2014

Conditionally drops the schema only if it already exists.

Syntax:

DROP SCHEMA  [ IF EXISTS ] schema_name

Check out our 1000+ SQL Exercises with solution and explanation to improve your skills.

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