Recovery from Constraint Violations (IDS)

If you use the ADD CONSTRAINT clause to add a constraint in the enabled mode, you receive an error message because existing rows would violate the constraint. For a procedure to add the constraint successfully, see Adding a Constraint That Existing Rows Violate (IDS).

DROP CONSTRAINT Clause

Use the DROP CONSTRAINT clause to drop a named constraint.

DROP CONSTRAINT Clause:
 
                         .-,----------.
                         V            |
|--DROP CONSTRAINT--+-(----constraint-+--)-+--------------------|
                    '-constraint-----------'

Element	Description	Restrictions	Syntax
constraint	Constraint to be dropped	Must exist.	Database Object Name, p. Database Object Name

To drop an existing constraint, specify the DROP CONSTRAINT keywords and the name of the constraint. Here is an example of dropping a constraint:

ALTER TABLE manufact DROP CONSTRAINT con_name

If no name is specified when the constraint is created, the database server generates the name. You can query the sysconstraints system catalog table for the name and owner of a constraint. For example, to find the name of the constraint placed on the items table, you can issue the following statement:

SELECT constrname FROM  sysconstraints
   WHERE tabid = (SELECT tabid FROM systables
      WHERE tabname = 'items')

When you drop a primary-key or unique constraint that has a corresponding foreign key, the referential constraints are dropped. For example, if you drop the primary-key constraint on the order_num column in the orders table and order_num exists in the items table as a foreign key, that referential relationship is also dropped.

You cannot use the MODIFY NEXT SIZE clause of the ALTER TABLE statement to change the size of the next extent of any system catalog table, even if you are user informix.

MODIFY NEXT SIZE Clause

Use the MODIFY NEXT SIZE clause to change the size of new extents.

MODIFY NEXT SIZE Clause:
 
|--MODIFY NEXT SIZE--kilobytes----------------------------------|

Element	Description	Restrictions	Syntax
kilobytes	Length (in kilobytes) assigned here to the next extent for this table	Specification cannot be a variable, and (4(page size)) ≤ kilobytes ≤ (chunk size)	Expression, p. Expression

The minimum extent size is 4 times the disk-page size. For example, on a system with 2-kilobyte pages, the minimum length is 8 kilobytes. The maximum length is equal to the chunk size. This example specifies an extent size of 32 kilobytes:

ALTER TABLE customer MODIFY NEXT SIZE 32

This clause cannot change the size of existing extents. You cannot change the size of existing extents without unloading all of the data.

To change the size of existing extents, you must unload all the data, drop the table, modify the first-extent and next-extent sizes in the CREATE TABLE definition in the database schema, re-create the database, and reload the data. For information about how to optimize extent sizes, see your IBM Informix Administrator's Guide.

LOCK MODE Clause

Use the LOCK MODE keywords to change the locking granularity of a table.

LOCK MODE Clause:
 
|--LOCK MODE--(--+-PAGE---------+--)----------------------------|
                 +-ROW----------+
                 |  (1)         |
                 '--------TABLE-'

Notes:

Extended Parallel Server only

The following table describes the locking-granularity options available.

Granularity: Effect
PAGE: Obtains and releases one lock on a whole page of rows
This is the default locking granularity. Page-level locking is especially useful when you know that the rows are grouped into pages in the same order that you are using to process all the rows. For example, if you are processing the contents of a table in the same order as its cluster index, page locking is especially appropriate.
ROW: Obtains and releases one lock per row
Row-level locking provides the highest level of concurrency. If you are using many rows at one time, the lock-management overhead can become significant. You can also exceed the maximum number of locks available, depending on the configuration of your database server.
TABLE (XPS only): Places a lock on the entire table
This type of lock reduces update concurrency in comparison to row and page locks. A table lock reduces the lock-management overhead for a table. Multiple read-only transactions can still access the table.