Apache Derby Schemas

Derby - Indexes

Derby - Project 01

Apache Derby schema is a collection of different types of object which are stored in a schema. Each schema has a unique name and the name should not exceed 128 characters. As a standard practice, a schema name should not start with SYS.

Let us see how to create a schema and create a table in that schema.

Create Schema

We can use the Create Schema statement to create an Apache Derby schema.

Syntax:

ij> CREATE SCHEMA { [ schemaName AUTHORIZATION user-name ] | [ schemaName ] | [ AUTHORIZATION user-name ] };

We will create a schema name “employee_schema” and create a table “Emp_Detail” in that schema.

Command:

ij> CREATE SCHEMA AUTHORIZATION employee_schema;

ij> CREATE TABLE employee_schema.Emp_Detail ( Id INT NOT NULL GENERATED ALWAYS AS IDENTITY,
   Emp_Name VARCHAR(255),
   Emp_Salary INT NOT NULL,
   Emp_Location VARCHAR(255),
   Emp_Phone BIGINT
)

Output:

Show schemas

We can use Show schemas to see the list of all schema.

Syntax:

ij> show schemas;

Command:

ij> show schemas;

Output:

Drop Schema

We can use the Drop Schema command to drop a schema. We need to note here that a schema is dropped in only those cases if the objects present in the schema are dropped.

Syntax:

ij> DROP SCHEMA my_schema RESTRICT;

Command:

ij> DROP TABLE employee_schema.Emp_Detail;

ij> DROP SCHEMA EMPLOYEE_SCHEMA RESTRICT;

Output: