Filter
Top Products
CHAPTER 3 Working with Database Objects
123
By contrast, when you specify
UNIQUE or PRIMARY KEY, each value can occur
only once in that column. For example, in the
employee table, each value of
ss_number, the employee’s social security number, can occur just once
throughout that column. This uniqueness extends to NULL values. Thus, a
column specified as
UNIQUE must also have the constraint NOT NULL.
Altering tables
This section describes how to change the structure of a table using the ALTER
TABLE
statement.
Example 1
The following command adds a column to the skill table to allow space for an
optional description of the skill:
ALTER TABLE skill
ADD skill_description CHAR( 254 )
Example 2
The following statement changes the name of the skill_type column to
classification:
ALTER TABLE skill
RENAME skill_type TO classification
Example 3
The following statement deletes the classification column.
ALTER TABLE skill
DELETE classification
Example 4
The following statement changes the name of the entire table:
ALTER TABLE skill
RENAME qualification
These examples show how to change the structure of the database. The ALTER
TABLE
statement can change many characteristics of a table—foreign keys can
be added or deleted, and so on. However, you cannot use
MODIFY to change
table or column constraints. Instead, you must
DELETE the old constraint and
ADD the new one. In all these cases, once you make the change, stored
procedures, views, and any other item referring to this column will no longer
work.
For a complete description of the ALTER TABLE command, see Adaptive
Server IQ Reference Manual. For information about building constraints into
table definitions using ALTER TABLE, see Chapter 7, “Ensuring Data
Integrity”