Once you define a table hierarchy, you cannot modify the structure (columns) of the existing tables. However, you can modify the behavior of tables in the hierarchy. Table 4 shows the table behavior that you can modify in a table hierarchy and the syntax that you use to make modifications.
| Table Behavior | Syntax | Considerations |
|---|---|---|
| Constraint definitions | ALTER TABLE | To add or drop a constraint, use the ADD CONSTRAINT or DROP CONSTRAINT clause. For more information, see Constraints on Tables in a Table Hierarchy. |
| Indexes | CREATE INDEX, ALTER INDEX | For more information, see Adding Indexes to Tables in a Table Hierarchy and the CREATE INDEX and ALTER INDEX statements in the IBM Informix: Guide to SQL Syntax. |
| Triggers | CREATE/DROP TRIGGER | You cannot drop an inherited trigger. However, you can drop a trigger from a supertable or add a trigger to a subtable to override an inherited trigger. For information about how to modify triggers on supertables and subtables, see Triggers on Tables in a Table Hierarchy. For information about how to create a trigger, see the IBM Informix: Guide to SQL Tutorial. |
All existing subtables automatically inherit new table behavior when you modify a supertable in the hierarchy.
You can alter or drop a constraint only in the table on which it is defined. You cannot drop or alter a constraint from a subtable when the constraint is inherited. However, a subtable can add additional constraints. Any additional constraints that you define on a table are also inherited by any subtables that inherit from the table that defines the constraint. Because constraints are additive, all inherited and current (added) constraints apply.
When you define an index on a supertable in a hierarchy, any subtables that you define under that supertable also inherit the index. Suppose you have a table hierarchy that contains the tables tab_a, tab_b, and tab_c where tab_a is a supertable to tab_b, and tab_b is a supertable to tab_c. If you create an index on a column of tab_b, then that index will exist on that column in both tab_b and tab_c. If you create an index on a column of tab_a, then that index will span tab_a, tab_b, and tab_c.
Indexes, unique constraints, and primary keys are all closely related. When you specify a unique constraint or primary key, the database server automatically creates a unique index on the column. Consequently, a primary key or unique constraint that you define on a supertable applies to all the subtables. For example, suppose there are two tables (a supertable and subtable), both of which contain a column emp_id. If the supertable specifies that emp_id has a unique constraint, the subtable must contain emp_id values that are unique across both the subtable and the supertable.
You cannot drop an inherited trigger. However, you can create a trigger on a subtable to override a trigger that the subtable inherits from a supertable. Unlike constraints, triggers are not additive; only the nearest trigger on a supertable in the hierarchy applies.
If you want to disable the trigger that a subtable inherits from its supertable, you can create an empty trigger on the subtable to override the trigger from the supertable. Because triggers are not additive, this empty trigger executes for the subtable and any subtables under the subtable, which are not subject to further overrides.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]