CREATE SCHEMA
The CREATE SCHEMA statement creates a name space for tables, views, and methods. You can use it to create multiple objects in one SQL statement.
- You can create a table, view, or method in an existing schema in two ways:
- You can create it as part of a CREATE SCHEMA statement.
- You can specify a schema name as part of the object name when you issue a standalone CREATE TABLE, CREATE VIEW, or CREATE METHOD statement. If you use the latter method (using CREATE TABLE, for example), you must specify a schema name that already exists.
- To create an object in a new schema, specify a new schema name in the CREATE SCHEMA statement and then create the table, view, or method as part of the CREATE SCHEMA statement.
- The AUTHORIZATION clause names the owner of the schema. If you do not specify an owner, the owner is the user of the SQL session. Only an administrator can specify a user name other than their own user name in the AUTHORIZATION clause.
- If you issue a standalone CREATE TABLE, CREATE VIEW, or CREATE METHOD statement (meaning that it s not embedded in a CREATE SCHEMA statement) and you do not specify a schema name as part of the CREATE statement, Blackfish SQL uses the following algorithm to assign the new object to a schema:
- If you have explicitly created a schema that has the same name as your current user name, then you have created a personal default schema. The table, view, or method belongs to your default schema.
- If you have not created a personal default schema, the table, view, or method belongs to the DEFAULT_SCHEMA schema.
- You can create schemas with names other than your user name, but you cannot create schemas that have other users' names unless you have administrative privileges.
- All objects created in early versions of Blackfish SQL that did not support schemas belong to the DEFAULT_SCHEMA schema when migrated to version 7 or later.
- A semicolon marks the end of the CREATE SCHEMA statement. There cannot be any semicolons between the schema elements.
- All the statements in the schema element list are executed as one statement in the same transaction.
Default Schemas
Initially your default schema is DEFAULT_SCHEMA. When you create a schema with the same name as your current user name, that schema becomes your default schema. You can create objects without specifying a schema name and those objects automatically belongs to your default schema. Assume, for example, that user PETER created a schema PETER. At a later time, PETER creates a table without specifying a schema. The table belongs to the PETER schema.
In the following example, the created table would actually be named PETER.FOO.
[USER: PETER]
CREATE TABLE FOO (COL1 INT, COL2 VARCHAR);
You are permitted to create schemas with names other than your user name, but they can never be your default schema. You cannot create a schema that has another user's name unless you are an administrator.
Syntax
<create schema statement> ::=
CREATE SCHEMA [ <schema name> ]
[ AUTHORIZATION <user name> ]
<schema element list>
<schema name> ::=
<SQL identifier>
<schema element commalist> ::=
<create table statement>
| <create view statement>
| <create method statement>
| <grant statement>
See GRANT for more information about GRANT statements.
Example
The following statement creates the schema BORIS with a table T1 and a view V1. In this schema, the user BJORN is granted SELECT privileges on view V1. After this statement executes, BORIS is the default schema for user BORIS.
[USER: BORIS]
CREATE SCHEMA BORIS
CREATE TABLE T1 (C1 INT, C2 VARCHAR)
CREATE VIEW V1 AS SELECT C2 FROM T1
GRANT SELECT ON V1 TO BJORN;
DROP SCHEMA
The DROP SCHEMA statement deletes the specified schema. If the command is used without options, it is the same as specifying the RESTRICT option: the schema to be dropped must be empty. The command fails if the schema contains any objects.
- The RESTRICT option causes the statement to fail if there are any objects in the schema. RESTRICT is the default option.
- Used with the CASCADE option, DROP SCHEMA deletes the named schema including all of its tables, views, foreign key dependencies, and methods.
WARNING: The DROP SCHEMA command used with the CASCADE option is extremely powerful and should be used with caution. When this command is issued, it drops the schema and all of its objects and dependencies without any chance to change your mind. There is no undo.
TIP: If you want to drop a schema but wish to preserve some of its tables, use the ALTER TABLE command to assign the tables to another schema. For example:
ALTER TABLE OLDSCHEMA.JOBS
RENAME TO NEWSCHEMA.JOBS;
Syntax
<drop schema statement> ::=
DROP SCHEMA <schema name> [ CASCADE | RESTRICT ]
Examples
- The following two statements are the same: they drop the schema BORIS; they both fail if the schema contains any objects.
DROP SCHEMA BORIS;
DROP SCHEMA BORIS RESTRICT;
- The following statement drops the schema BORIS and all of its tables, views, and methods. It also drops any dependent views and foreign keys.
DROP SCHEMA BORIS CASCADE;
CREATE TABLE
The CREATE TABLE statement creates a Blackfish SQL table. Each column definition must include at least a column name and data type. Optionally, you can specify a default value for each column, along with uniqueness constraints.
You can also optionally specify a foreign key and primary key. Blackfish SQL supports the use of one or more columns as a primary key or foreign key.
Specifying Schemas
To create a table in a particular schema, specify the schema name as part of the table name:
CREATE TABLE SOMESCHEMA.MYTABLE(. . .);
If you do not specify a schema name, the table is created in your default schema. See CREATE SCHEMA for more information about schemas.
Tracking Data Changes for DataExpress
Note: This feature is supported for Blackfish SQL for Java, only.
If you specify
RESOLVABLE as part of the table definition, Blackfish SQL keeps track of changes made to the data. The recorded changes are available to the DataExpress application, but not to SQL. The default is
NOT RESOLVABLE.
Overriding Consistency Checks
The NO CHECK option creates the foreign key without checking the consistency at creation time. Use this option with caution.
Using AutoIncrement Columns with SQL
To create or alter a column to have the Autoincrement property using SQL, add the AUTOINCREMENT keyword to your <table element> definition.
The following statement creates table T1 with an integer autoincrement column called C1:
CREATE TABLE T1 ( C1 INT AUTOINCREMENT, C2 DATE, C3 CHAR(32) );
To obtain the Autoincrement value of a newly inserted row using the JDS JDBC driver (JVM version 1.3 or earlier), call the JdsStatement.getGeneratedKeys method. This method is also available in the statement interface of JDBC 3 in JVM 1.4.)
Specifying Column Position
In the columns definition, use the POSITION option to force a column to be in a particular position in the table (second column, for example). The following code snippet forces column COLD to be the second column:
CREATE TABLE(COLA INT, COLB STRING, COLC INT, COLD STRING POSITION 2);
Syntax
<create table statement> ::=
CREATE TABLE <table name> ( <table element commalist> )
<table name> ::=
[ <schema name> . ] <SQL identifier>
<schema name> ::=
<SQL identifier>
<table element> ::=
<column definition>
| <primary key>
| <unique key>
| <foreign key>
| [NOT] RESOLVABLE
<column definition> ::=
<column name> <data type>
[ DEFAULT <default value> ]
[ [NOT] NULL ]
[ AUTOINCREMENT ]
[ POSITION <integer literal> ]
[ [ CONSTRAINT <constraint name> ] PRIMARY KEY ]
[ [ CONSTRAINT <constraint name> ] UNIQUE ]
[ [ CONSTRAINT <constraint name> ] <references definition> ]
<column name> ::=
<SQL identifier>
<default value> ::=
<literal>
| <current date function>
<current date function> ::=
CURRENT_DATE
| CURRENT_TIME
| CURRENT_TIMESTAMP
<primary key> ::=
[ CONSTRAINT <constraint name> ] PRIMARY KEY <column name commalist>)
<unique key> ::=
[ CONSTRAINT <constraint name> ] UNIQUE ( <column name commalist> )
<foreign key> ::=
[ CONSTRAINT <constraint name> ] FOREIGN KEY ( <column name commalist> )
<references definition>
<references definition> ::=
REFERENCES <table name> [ ( <column name commalist> ) ]
[ ON DELETE <action> ]
[ ON UPDATE <action> ]
[ NO CHECK ]
<action> ::=
NO ACTION
| CASCADE
| SET DEFAULT
| SET NULL
<constraint name> ::=
<SQL identifier>
Example 1
The following statement creates a table with four columns. The CustId column is the primary key and the OrderDate column has the current time as the default value.
CREATE TABLE Orders ( CustId INTEGER PRIMARY KEY, Item VARCHAR(30),
Amount INT, OrderDate DATE DEFAULT CURRENT_DATE);
Example 2
The following statement creates a table that uses two columns for the primary key constraint:
CREATE TABLE T1 (C1 INT, C2 STRING, C3 STRING, PRIMARY KEY (C1, C2));
Example 3
The following statement creates a table T1 in the BORIS schema:
CREATE TABLE BORIS.T1 (C1 INT, C2 STRING, C3 STRING);
ALTER TABLE
The ALTER TABLE statement performs the following operations:
- Adds or removes columns in a Blackfish SQL table
- Sets or drops column defaults and NULLability
- Changes column data types
- Adds or drops primary key, unique key, and foreign key column constraints and table constraints; changes the referenced table and type of action for these constraints
- Renames columns
- Renames tables; this also allows you to move tables from one schema to another
- Adds or drops the RESOLVABLE table property
- Repositions columns within the table
Syntax
<alter table statement> ::=
ALTER TABLE <table name> <change definition commalist>
<table name> ::= [ <schema name> . ]<SQL identifier>
<change definition> ::=
<add column element>
| <drop column element>
| <alter column element>
| <add constraint>
| <drop constraint>
| [RENAME] TO <table name>
| [NOT] RESOLVABLE
<add column element> ::= ADD [COLUMN] <column definition>
<column definition> ::=
<column name> <data type> [ DEFAULT <default value> ]
[ [NOT] NULL ]
[ AUTOINCREMENT ]
[ POSITION <integer literal> ]
[ [ CONSTRAINT <constraint name> ] PRIMARY KEY ]
[ [ CONSTRAINT <constraint name> ] UNIQUE ]
[ [ CONSTRAINT <constraint name> ] <references definition> ]
<drop column element> ::= DROP [COLUMN] <column name>
<alter column element> ::=
ALTER [COLUMN] <column name> [TYPE] <data type>
| ALTER [COLUMN] <column name> SET DEFAULT <default-value>
| ALTER [COLUMN] <column name> DROP DEFAULT
| ALTER [COLUMN] <column name> [NOT] NULL
| ALTER [COLUMN] <column name> [RENAME] TO <column name>
| ALTER [COLUMN] <column name> [POSITION] <integer literal>
| ALTER [COLUMN] <column name> AUTOINCREMENT
| ALTER [COLUMN] <column name> DROP AUTOINCREMENT
<add constraint> ::= ADD <base table constraint>
<base table constraint> ::=
<primary key> | <unique key> | <foreign key>
<drop constraint> ::= DROP CONSTRAINT <constraint name>
<primary key> ::=
[ CONSTRAINT <constraint name> ]
PRIMARY KEY <column name commalist>)
<unique key> ::=
[ CONSTRAINT <constraint name> ]
UNIQUE ( <column name commalist> )
<foreign key> ::=
[ CONSTRAINT <constraint name> ]
FOREIGN KEY ( <column name commalist> )
<references definition>
<references definition> ::=
REFERENCES <table name> [ ( <column name commalist> ) ]
[ ON DELETE <action> ]
[ ON UPDATE <action> ]
[ NO CHECK ]
<action> ::=
NO ACTION
| CASCADE
| SET DEFAULT
| SET NULL
<constraint name> ::= <SQL identifier>
In ALTER [COLUMN], the optional COLUMN keyword is included for SQL compatibility. It has no effect.
Example
The following example adds a column named ShipDate to the Orders table and drops the Amount column from the table.
ALTER TABLE Orders
ADD ShipDate DATE,
DROP Amount;
The following example moves the Jobs table from the OldSchema schema to the NewSchema schema.
ALTER TABLE OldSchema.Jobs
RENAME TO NewSchema.Jobs;
DROP TABLE
The DROP TABLE statement deletes a table and its indexes from a Blackfish SQL database.
- The RESTRICT option guarantees that the statement will fail if there are foreign key or view dependencies on the table.
- The CASCADE option causes all dependent views and foreign keys to be dropped when the table is dropped.
- Specifying neither RESTRICT nor CASCADE drops the table and any foreign keys that reference it. The statement fails if there are dependent views.
Syntax
<drop table statement> ::=
DROP TABLE [ <schema name> . ]<table name> [ CASCADE|RESTRICT ]
<schema name> ::= <SQL identifier>
Examples
- The following statement drops the Orders table only if there are no dependent views. If there are dependent foreign keys, the statement succeeds and the foreign keys are dropped.
- The following statement drops the Orders table only if there are no dependent views or foreign keys.
DROP TABLE Orders RESTRICT;
- The following statement drops the Orders table. All dependent views and dependent foreign keys are also dropped.
DROP TABLE Orders CASCADE;
CREATE VIEW
The CREATE VIEW statement creates a derived table by selecting specified columns from existing tables. Views provide a way of accessing a consistent subcollection of the data stored in one or more tables. When the data in the underlying tables changes, the view reflects this change.
Views look just like ordinary database tables, but they are not physically stored in the database. The database stores only the view definition, and uses this definition to filter the data when a query referencing the view occurs.
When you create a view, you can specify names for the columns in the view using the optional <column name commalist> portion of the syntax. If you do not specify column names, the names of the table columns from which the view columns are derived are used. If you do specify column names, you must specify exactly the number of columns that will be returned from the SELECT query.
The WITH CHECK OPTION clause causes a runtime check to be performed to ensure that an inserted or updated row will not be filtered out by the WHERE clause of the view definition.
Views are updatable only under limited conditions. If you want to execute INSERT, UPDATE, or DELETE on a view, it must meet all of the following conditions:
- It is derived from a single table.
- None of the columns are calculated.
- The SELECT clause that defines the view does not contain the DISTINCT keyword.
- The SELECT expression that defines the view does not contain any of the following:
- Subqueries
- A HAVING clause
- A GROUP BY clause
- An ORDER BY clause
- Aggregate functions
- methods
Syntax
<create view statement> ::=
CREATE VIEW <view name> [ ( <column name commalist> ) ]
AS <select expression> [ WITH CHECK OPTION ]
<view name> ::=
[ <schema name> . ] <SQL identifier>
Example
The following statement creates a view V1 from table T1. The columns in the view are named C1 and C2.
CREATE VIEW V1(C1,C2)
AS SELECT C8+C9, C6 FROM T1 WHERE C8 < C9;
ALTER VIEW
The ALTER VIEW statement modifies a view without losing dependent views and existing GRANTs. This statement can be used to change the name of a view, the columns that comprise the view, and whether the view has the WITH CHECK OPTION constraint.
Note that after ALTER VIEW executes, it is possible that there are dependent views that are no longer valid.
Syntax
<alter view statement> ::=
ALTER VIEW <view name> [ ( <column name commalist> ) ]
AS <select expression> [ WITH CHECK OPTION ]
Example
The following statements show how the ALTER VIEW statement can be used to validate an invalid view. The first two statements create a table and then create a view based on that table. The third statement, SELECT, succeeds.
CREATE TABLE T1 (C1 INT, C2 VARCHAR);
CREATE VIEW V1 AS SELECT C1, C2 FROM T1;
SELECT * FROM V1;
The following statement changes a column name in the table.
ALTER TABLE T1 ALTER COLUMN C1 RENAME TO ID;
The next SELECT statement therefore fails because there is no longer a C1 column in the table T1, which is accessed by view V1.
The following ALTER VIEW statement changes the definition of the view, so that the next SELECT statement succeeds.
ALTER VIEW V1 (C1, C2) AS SELECT ID, C2 FROM T1;
SELECT * FROM V1;
DROP VIEW
The DROP VIEW statement drops the named view. It fails if there are dependencies on the view.
- The RESTRICT option is the same as specifying no options: the statement fails if there are dependencies on the view.
- The CASCADE option drops the view and any dependent views.
Syntax
<drop view statement> ::=
DROP VIEW <view name> [ CASCADE | RESTRICT ]
Example
The following code creates a table and two views:
CREATE TABLE T1 (C1 INT, C2 VARCHAR);
CREATE VIEW V1 AS SELECT C1, C2 FROM T1;
CREATE VIEW V2 AS SELECT C1, C2 FROM V1;
The following statement fails because view V1 has a dependent view (V2).
The following statement succeeds and both V1 and V2 are dropped.
CREATE INDEX
The CREATE INDEX statement creates an index for a Blackfish SQL table. Each column can be ordered in ascending or descending order. The default value is ascending order.
Syntax
<create index statement> ::=
CREATE [UNIQUE] [CASEINSENSITIVE] INDEX <index name>
ON <table name> ( <index element commalist> )
<table name> ::=
[ <schema name> . ]<SQL identifier>
<index name> ::=
<SQL Identifier>
<index element> ::=
<column name> [ DESC|ASC ]
Example
The following statement generates a non-unique, case-sensitive, ascending index on the Item column of the Orders table:
CREATE INDEX OrderIndex ON Orders (Item ASC);
DROP INDEX
The DROP INDEX statement deletes an index from a Blackfish SQL table.
Syntax
<drop index statement> ::=
DROP INDEX <index name> ON <table name>
Example
The following statement deletes the OrderIndex index from the Orders table:
DROP INDEX OrderIndex ON Orders;
CREATE METHOD
The CREATE METHOD statement makes a stored procedure or a UDF implemented in Java or a .NET language (e.g., Delphi, C#, or VB.NET) available for use in Blackfish SQL. The class files for the code must be added to the classpath of the Blackfish SQL server process before use. See Stored Procedures and UDFs for details about how to implement stored procedures and UDFs for Blackfish SQL.
To create a method in a particular schema, specify the schema name as part of the table name:
CREATE METHOD SOMESCHEMA.MYMETHOD AS . . .
If you do not specify a schema name, the method is assigned to a schema as follows:
- If you have created a personal default schema (a schema that has the same name as your user name), the method is created in that schema.
- If you have have not created a personal default schema, the method is created in the DEFAULT_SCHEMA schema.
See CREATE SCHEMA for more information about schemas. The AUTHORIZATION clause causes the called stored procedure to be run as if the username in the AUTHORIZATION clause were the actual user. If this clause is omitted, the current_user is used as the actual user during method calls. This feature allows the current user controlled access to tables and views that would not otherwise be accessible. Syntax
<create method statement> ::=
CREATE METHOD <method name> [AUTHORIZATION <username>]
AS <method definition>
<method name> ::=
[ <schema name> . ] <SQL identifier>
<schema name> ::= <SQL identifier>
<method definition> ::= <string literal>
Data Types