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:
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:
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:
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 [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
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join