Sybase Adaptive Server IQ 12.4.2, Administration And Performance Manual

Page: 143 / 536

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”