|
|
|
Valid in: SQL, ESQL
The Create Schema statement creates a named collection of database objects.
|
|
|
The Create Schema statement has the following format:
[EXEC SQL] CREATE SCHEMA AUTHORIZATION schema_name [object_definition {object_definition}];
Specifies the effective user for the session issuing the CREATE SCHEMA statement.
Is a CREATE TABLE, CREATE VIEW, or GRANT statement.
|
|
|
The Create Schema statement creates a named collection of database objects (tables, views, and privileges). Each user has a maximum of one schema per database. If an error occurs within the CREATE SCHEMA statement, the entire statement is rolled back.
The statements within the CREATE SCHEMA statement must not be separated by semicolon delimiters; however, the CREATE SCHEMA statement must be terminated by placing a semicolon after the last object definition statement (CREATE TABLE, CREATE VIEW, or GRANT).
If object definitions are omitted, an empty schema is created.
To issue grant statements in a CREATE SCHEMA statement, you must have the required privileges. Specifically, to grant a privilege on an object you do not own, you must have been granted the privilege WITH GRANT OPTION.
If an invalid GRANT statement is issued within a CREATE SCHEMA statement, the outcome is determined as follows:
For example, if user andre has been granted SELECT WITH GRANT OPTION on table tony.mytable and issues the following GRANT statement within a CREATE SCHEMA statement:
grant select, insert on tony.mytable to fred
user fred is granted SELECT privilege but not INSERT privilege, and a warning is issued.
If a CREATE SCHEMA is issued specifying an existing schema (schema_name), the DBMS Server issues an error. To add objects to an existing schema, issue the required CREATE statements outside of a CREATE SCHEMA statement.
If no schema exists for the effective user identifier, one is implicitly created when any database object is created. If a CREATE SCHEMA statement is subsequently issued for the user, the DBMS Server returns an error.
If, within a CREATE SCHEMA statement, tables are created that have referential constraints, the order of CREATE TABLE statements is not significant. This differs from the requirements for creating tables with referential constraints outside of a CREATE SCHEMA statement, where the referenced table must exist before creating a constraint that references it. For details about referential constraints, see Create Table.
Other users can reference objects in your schema if you have granted them the required permissions. To access an object in a schema other than the schema for the effective user of the session, specify the object name as follows:
schema.object
For example, user harry can select data from the employees table of the accounting group (if accounting has granted harry select permission). Harry can issue the following SELECT statement:
select lname, fname from accounting.employees
|
|
|
You cannot use host language variables in an embedded Create Schema statement.
|
|
|
All users.
|
|
|
The Create Schema statement takes an exclusive lock on a page in the iischema catalog. Locking for the individual statements (create table, create view, and grant) is described in the statement descriptions in this chapter. Locks are acquired by the individual create statements within the Create Schema statement, but released only when the create schema statement itself is committed. If the Create Schema statement contains create statements that acquire locks in excess of the maximum configured for the DBMS Server, the create schema statement is aborted.
|
|
|
|
|
|
Create a schema for the accounting user:
create schema authorization accounting
create table employees (lname char(30) not null,
fname char(30) not null,
salary money,
dname char(10)
references dept(deptname),
primary key (lname, fname)
create table dept(deptname char(10) not null unique,
location char(15),
budget money,
expenses money default 0)
create view mgr(mlname, mfname, mdname) as
select lname, fname, deptname from employees,dept
where dname = deptname
grant references(lname, fname)
on table employees to harry;