Databases Reference
In-Depth Information
6.5.2 Using ALTER
An ALTER statement will follow a similar syntax to the CREATE statement for
the same type of object. ALTER TABLE, for example, lets you manage table
columns, table constraints, column constraints, and so forth. ALTER INDEX lets
you change the index type (as clustered or nonclustered) and change the index's
key and nonkey columns. With most object types, some actions will be restricted
and changes made to one object might impact another.
The ANSI SQL-99 standard ALTER TABLE statement uses the following syntax:
ALTER TABLE
table_name
{[ADD [COLUMN]
column_definition
] |
[ALTER [COLUMN]
column_name
{SET DEFAULT
default
| DROP DEFAULT}] |
[DROP [COLUMN]
column_name
RESTRICT | CASCADE] |
[ADD
table_constraint
] |
[DROP CONSTRAINT
constraint_name
RESTRICT | CASCADE]}
Though not specifically described in the standard syntax, most DBMSs let
you alter other aspects of the column definition, such as column constraints, in
addition to column default values. You are limited to taking one action each time
you run an ALTER statement. For example, if you want to add a column, drop
anther column, and add a constraint to the table, you would need to run the
ALTER TABLE statement three times.
6.5.3 Using DROP
The DROP command is used to delete objects of that type. The DROP statement
is different from other statements in that you can drop multiple objects of the
same type in a single statement. The syntax for the DROP TABLE command is,
for example:
DROP TABLE
table_name,
...
Your DBMS will put restrictions on dropping objects, preventing you from
creating illegal conditions. For example, most DBMSs will not let you drop a
table (by default) if it is referenced by any other table's foreign key.
SELF-CHECK
•
Describe the purpose of CREATE, ALTER, and DELETE statements.
•
Explain the role of DDL statements in the SQL language.
