DDL Statements

This section provides BNF notation for DDL statements.

Tables

The notations below are to create, modify, and drop database tables, respectively:

CREATE TABLE <table_name>
(
 column_name <datatype> [<column_constraint>,...]
                        [DEFAULT <default_value>],...
 [<table_constraint>,...]
 [physical_options]
)

ALTER TABLE <table_name>
{ <vendor_specific_add_column_clause> |
  <vendor_specific_alter_column_clause> |
  <vendor_specific_add_constraint_clause> |
  <vendor_specific_drop_constraint_clause>
}

Note

ALTER TABLE statement clauses vary for different implementations and can hardly be generalized. See Chapter 5 for more information.

DROP TABLE <table_name>

Indexes

The following two notations are to create and drop database indexes:

CREATE [UNIQUE] INDEX <index_name>
ON <table_name> (<column_name> [ASC|DESC],...)

DROP INDEX <index_name>

Views

The notations below are to create, modify, and drop database views, respectively:

CREATE VIEW <view_name> [(column_name,...)]
AS <select_statement>
[WITH CHECK OPTION]

ALTER VIEW <view_name>
<vendor_specific_alter_view_clause>

DROP VIEW <view_name>

Schemas

The following two notations are to create and to drop database schemas:

CREATE SCHEMA <schema_name> 
AUTHORIZATION <authorization_id>
<create_object_statement>,...
<grant_privilege_statement>,...

Note

In Oracle, the schema_name token is invalid. You can create schemas in Oracle in your own schema only, and only with your own authorization_id.

DROP SCHEMA <schema_name> RESTRICT

Note

The foregoing syntax is for DB2 only; Oracle and MS SQL Server don't have DROP SCHEMA statements in their syntaxes.

Stored procedures

The BNF notation to create a stored procedure follows:

CREATE PROCEDURE <procedure_name> [<parameter_section>]
<procedure_definition>

Note

The preceding specification is generic; the actual syntax is implementation-specific. Refer to vendor-specific documentation for details.

The following notation is to drop a stored procedure:

DROP PROCEDURE <procedure_name>

User-defined functions

The BNF notation to create a user-defined function follows:

CREATE FUNCTION <function_name>
<function_definition_includes_return_statement>

Note

The preceding specification is generic; the actual syntax is implementation-specific. Refer to vendor-specific documentation for details.

The following notation is to drop a user-defined function:

DROP FUNCTION <function_name>

Triggers

The BNF notation to create a trigger follows:

CREATE TRIGGER <trigger_name>
[BEFORE | AFTER] 
{INSERT | UPDATE | DELETE}
ON <table_name>
[FOR EACH ROW]
<trigger_body>

Note

The preceding syntax describes only basic trigger functionality; the actual implementations have more options.

The following notation is to drop a trigger:

DROP TRIGGER [qualifier.]<trigger_name>