Schemas

A schema is a logical database object holder. SQL99 defines a schema as a named group of related objects. 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.

CREATE SCHEMA statement

The CREATE SCHEMA statement has different meanings in SQL99, Oracle, DB2, and MS SQL Server.

SQL99

SQL99 states that the CREATE SCHEMA statement creates a group of objects that somehow logically relate to each other; the group has a name that is called schema name. Also, you can grant privileges on these objects within the CREATE SCHEMA statement. The syntax is

CREATE SCHEMA
			 {<schema_name> | AUTHORIZATION <authorization_id> |
			 <schema_name> AUTHORIZATION <authorizat_id> }
			 <create_object_statement>,...
			 <grant_privilege_statement>,...

The schema creator usually owns the objects within the schema unless otherwise specified by using a different authorization_id.

Objects that can be created as a part of a schema include tables, views, domains, assertions, character sets, collations, and translations; you can grant any valid privileges within the grant_privilege_statement clause.

Note

Creation of domains, assertions, character sets, collations, and translations are not valid operations in all our three major databases even though they are all part of SQL99 standards.

Cross-References

The GRANT PRIVILEGE statement is covered in Chapter 12 (SQL and RDBMS Security).

Oracle 9i

We already mentioned that in Oracle terminology the word schema is almost identical to user. You still can use the CREATE SCHEMA statement in Oracle, but the only use for that operation would be to create multiple objects in a single transaction.

Note

The CREATE SCHEMA statement does not actually create a schema in Oracle. The schema is automatically created when you create a user (see Chapter 12).

The syntax for CREATE SCHEMA is

CREATE SCHEMA AUTHORIZATION
			 <schema=your_user_name> <create_object_statement>,...
			 <grant_privilege_statement>,... ;

The schema name must be the same as your Oracle user ID, otherwise the statement will fail. The valid objects to create include tables and views; you can grant any valid object privileges on them to anybody. The following example creates two tables in hypothetical schema ACMETEST (i.e., we assume that the statement is run by user ACMETEST that does not exist in the ACME test database) and gives permissions on them to user ACME:

CREATE
			 SCHEMA AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT CONSTRAINT
			 pk_addrprimary PRIMARY KEY, addr_custid_fn INT, addr_salesmanid_fn INT
			 CONSTRAINT fk_addr_salesman REFERENCES salesman (salesman_id_n) ON DELETE
			 CASCADE, addr_address_s VARCHAR2(60), addr_type_s VARCHAR2(8) CONSTRAINT
			 chk_addr_type CHECK (addr_type_s IN ('BILLING', 'SHIPPING')), addr_city_s
			 VARCHAR2(18) CONSTRAINT nn_addr_city NOT NULL, addr_state_s CHAR(2), addr_zip_s
			 VARCHAR2(10) NOT NULL, addr_country_s CHAR(3) ) CREATE TABLE salesman (
			 salesman_id_n INT CONSTRAINT pk_salesmanprim PRIMARY KEY, salesman_code_s
			 VARCHAR2 (2) CONSTRAINT uk_salescode UNIQUE, salesman_name_s VARCHAR2 (50) NOT
			 NULL, salesman_status_s CHAR (1) CONSTRAINT chk_salesstatus CHECK
			 (salesman_status_s in ('N', 'Y')) ) GRANT ALL ON salesman TO ACME GRANT ALL ON
			 address TO ACME ;

Note

The first CREATE TABLE statement will fail if you didn't create the two tables as a part of one transaction because it refers to the nonexistent SALESMAN table (constraint FK_ADDR_SALESMAN).

Note

If any statement within CREATE SCHEMA fails, all other statements are also ignored.

DB2 UDB 8.1

The DB2 CREATE SCHEMA statement seems to be closer to SQL99 standards. The syntax is

CREATE
			 SCHEMA {<schema_name> | AUTHORIZATION <authorization_id> |
			 <schema_name> AUTHORIZATION <authorization_id> }
			 <create_object_statement>,...
			 <grant_privilege_statement>,...

The valid objects to create within the create_object_statement clause are tables, views, and indexes. The owner of the schema is either authorization_id or (if not specified) the user who issued the CREATE SCHEMA statement:

CREATE
			 SCHEMA ACMETEST AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT NOT
			 NULL CONSTRAINT pk_addrprimary PRIMARY KEY, addr_custid_fn INT,
			 addr_salesmanid_fn INT CONSTRAINT fk_addr_salesman REFERENCES salesman
			 (salesman_id_n) " ON DELETE CASCADE, addr_address_s VARCHAR(60), addr_type_s
			 VARCHAR(8) CONSTRAINT chk_addr_type CHECK (addr_type_s IN ('BILLING',
			 'SHIPPING')), addr_city_s VARCHAR(18) CONSTRAINT nn_addr_city NOT NULL,
			 addr_state_s CHAR(2), addr_zip_s VARCHAR(10) NOT NULL, addr_country_s CHAR(3) )
			 CREATE TABLE salesman ( salesman_id_n INT NOT NULL CONSTRAINT pk_salesmanprim
			 PRIMARY KEY, salesman_code_s VARCHAR (2) NOT NULL CONSTRAINT uk_salescode
			 UNIQUE, salesman_name_s VARCHAR (50) NOT NULL, salesman_status_s CHAR (1)
			 CONSTRAINT chk_salesstatus CHECK (salesman_status_s in ('N', 'Y')) ) GRANT ALL
			 ON salesman TO ACME GRANT ALL ON address TO ACME

MS SQL Server 2000

MS SQL Server provides this syntax to create a schema:

CREATE SCHEMA AUTHORIZATION
			 <owner> <create_object_statement>,...
			 <grant_privilege_statement>,...

The owner must have a valid security account in the database. The statement below assumes the existence of account ACMETEST:

CREATE
			 SCHEMA AUTHORIZATION ACMETEST CREATE TABLE address ( addr_id_n INT CONSTRAINT
			 pk_addrprimary PRIMARY KEY, addr_custid_fn INT, addr_salesmanid_fn INT
			 CONSTRAINT fk_addr_salesman REFERENCES salesman (salesman_id_n) ON DELETE
			 CASCADE, addr_address_s VARCHAR(60), addr_type_s VARCHAR(8) CONSTRAINT
			 chk_addr_type CHECK (addr_type_s IN ('BILLING', 'SHIPPING')), addr_city_s
			 VARCHAR(18) CONSTRAINT nn_addr_city NOT NULL, addr_state_s CHAR(2), addr_zip_s
			 VARCHAR(10) NOT NULL, addr_country_s CHAR(3) ) CREATE TABLE salesman (
			 salesman_id_n INT CONSTRAINT pk_salesmanprim PRIMARY KEY, salesman_code_s
			 VARCHAR (2) not null CONSTRAINT uk_salescode UNIQUE, salesman_name_s VARCHAR
			 (50) NOT NULL, salesman_status_s CHAR (1) CONSTRAINT chk_salesstatus CHECK
			 (salesman_status_s in ('N', 'Y')) ) GRANT ALL ON salesman TO ACME GRANT ALL ON
			 ADDRESS TO ACME