Java Reference
In-Depth Information
The following command creates a new schema for the user who is logged in. Thus, if user John Doe is
logged in to the database and issues this command, a new schema will be created, and John Doe will
be assigned as the owner of the schema.
CREATE SCHEME myschema
Now let us assume that our other friend Jack Smith is logged in to the database and wants to create a
schema for John Doe. Jack can issue the following command, which creates a new schema for John
Doe. It is traditional to use the first initial and last name as the user name. However, this is organization
specific.
CREATE SCHEMA johns_schema AUTHORIZATION jdoe
Alternatively, you can use the more complex form of the command by nesting several
CREATE
OBJECT
statements when you are creating a schema. You can also nest the appropriate
GRANT
and
REVOKE
commands, described toward the end of this chapter.
CREATE SCHEMA johns_schema AUTHORIZATION jdoe
CREATE TABLE products (
ProductID int (4) PRIMARY_KEY,
ProductName varchar (40) NOT NULL,
ProductPrice float(5) NOT NULL
)
GRANT ALL ON PRODUCTS TO jdoe // The GRANT command is explained later
in this chapter
We have just learned how to create a schema. Next, we will talk about how to delete and how to make
changes to the schema.
Managing a Schema
In most database management systems, you do not have to create a schema explicitly. If that is the
case, a schema simply becomes the collection of objects a particular user owns. As you change those
objects, you are, in effect, changing the schema for the user. When you explicitly create a schema as
shown in the preceding section, you are creating various objects as part of the schema. Again, changing
those objects changes the schema.
To manage objects within your schema, use one of the following
ALTER OBJECT
or
DROP OBJECT
commands, such as:
ALTER VIEW|TABLE
DROP VIEW|TABLE
The ALTER VIEW command is used to alter a previously created view (created by executing CREATE
VIEW without affecting dependent stored procedures or triggers and without changing permissions. The
basic syntax of the ALTER View command is shown below:
ALTER VIEW [ < database_name >
.
] [ < owner >
.
] view_name
[
(
column [
,
...i]
)
]
[ WITH < view_attribute > [
,
...i] ]
AS
select_statement
The ALTER TABLE command can be used to change the schema of the table that you create. The
following examples show various formats of the ALTER TABLE command:
1. Add a column to a table
ALTER TABLE [ ONLY ] table [ * ] ADD [ COLUMN ] column type
