Constraints can be added to existing tables with the ALTER TABLE
statement.
About this task
The
constraint name cannot be the same as any other constraint specified within
an ALTER TABLE statement, and must be unique within the table (this includes
the names of any referential integrity constraints that are defined). Existing
data is checked against the new condition before the statement succeeds.
- Creating and modifying unique constraints
- Unique constraints can be added to an existing table. The constraint name
cannot be the same as any other constraint specified within the ALTER TABLE
statement, and must be unique within the table (this includes the names of
any referential integrity constraints that are defined). Existing data is
checked against the new condition before the statement succeeds.
To define
unique constraints using the command line, use the
ADD CONSTRAINT option
of the ALTER TABLE statement. For example, the following statement adds a
unique constraint to the EMPLOYEE table that represents a new way to uniquely
identify employees in the table:
ALTER TABLE EMPLOYEE
ADD CONSTRAINT NEWID UNIQUE(EMPNO,HIREDATE)
To modify this constraint, you would have to drop it, and then
recreate it.
- Creating and modifying primary key constraints
- A primary key constraint can be added to an existing table. The constraint
name must be unique within the table (this includes the names of any referential
integrity constraints that are defined). Existing data is checked against
the new condition before the statement succeeds.
To add primary keys using
the command line, enter:
ALTER TABLE <name>
ADD CONSTRAINT <column_name>
PRIMARY KEY <column_name>
An existing constraint cannot be modified. To define another column,
or set of columns, as the primary key, the existing primary key definition
must first be dropped, and then recreated.
- Creating and modifying check constraints
- When a table check constraint is added, packages and cached dynamic SQL
that insert or update the table might be marked as invalid.
To add a table
check constraint using the command line, enter:
ALTER TABLE EMPLOYEE
ADD CONSTRAINT REVENUE CHECK (SALARY + COMM > 25000)
To modify
this constraint, you would have to drop it, and then recreate it.
- Creating and modifying foreign key (referential) constraints
- A foreign key is a reference to the data values in another table. There
are different types of foreign key constraints.
When a foreign key is added
to a table, packages and cached dynamic SQL containing the following statements
might be marked as invalid:
- Statements that insert or update the table containing the foreign key
- Statements that update or delete the parent table.
To add foreign keys using the command line, enter:
ALTER TABLE <name>
ADD CONSTRAINT <column_name>
FOREIGN KEY <column_name>
ON DELETE <action_type>
ON UPDATE <action_type>
The following examples
show the ALTER TABLE statement to add primary keys and foreign keys to a table:
ALTER TABLE PROJECT
ADD CONSTRAINT PROJECT_KEY
PRIMARY KEY (PROJNO)
ALTER TABLE EMP_ACT
ADD CONSTRAINT ACTIVITY_KEY
PRIMARY KEY (EMPNO, PROJNO, ACTNO)
ADD CONSTRAINT ACT_EMP_REF
FOREIGN KEY (EMPNO)
REFERENCES EMPLOYEE
ON DELETE RESTRICT
ADD CONSTRAINT ACT_PROJ_REF
FOREIGN KEY (PROJNO)
REFERENCES PROJECT
ON DELETE CASCADE
To modify this constraint, you would
have to drop it and then recreate it.
- Creating and modifying informational constraints
- To improve the performance of queries, you can add informational constraints
to your tables. You add informational constraints using the CREATE TABLE or
ALTER TABLE statement when you specify the NOT ENFORCED option
on the DDL.
Restriction: After you define informational constraints
on a table, you can only alter the column names for that table after you remove
the informational constraints.
To specify informational constraints
on a table using the command line, enter the following command for a new table:
ALTER TABLE <name> <constraint attributes> NOT ENFORCED
ENFORCED or NOT ENFORCED: Specifies whether the constraint is enforced
by the database manager during normal operations such as insert, update, or
delete.
- ENFORCED cannot be specified for a functional dependency (SQLSTATE 42621).
- NOT ENFORCED should only be specified if the table data is independently
known to conform to the constraint. Query results might be unpredictable if
the data does not actually conform to the constraint.
To modify this constraint, you would have to drop it and then
recreate it.