PUT Clause (IDS)

Use the PUT clause to specify the storage space (an sbspace) for a column that contains smart large objects. This clause can specify storage characteristics for a new column or replace the storage characteristics of an existing column. The syntax is similar to the PUT clause of the CREATE TABLE statement (page ***), but specifies only a single column, rather than a list of columns.

Element	Description	Restrictions	Syntax
column	Column to store in the specified sbspace	Must be a UDT, or a complex, BLOB, or CLOB data type	Identifier, p. Identifier
kilobytes	Number of kilobytes to allocate for the extent size	Must be an integer value	Literal Number, p. Literal Number
sbspace	Name of an area of storage for smart large objects	The sbspace must exist	Identifier, p. Identifier

When you modify the storage characteristics of a column, all attributes previously associated with the storage space for that column are dropped. When you want certain attributes to remain, you must specify those attributes again. For example, to retain logging, you must specify the log keyword again.

The format column.field is not valid here. That is, the smart large object that you are storing cannot be one field of a row type.

When you modify the storage characteristics of a column that holds smart large objects, the database server does not alter smart large objects that already exist, but applies the new storage characteristics only to those smart large objects that are inserted after the ALTER TABLE statement takes effect.

For more information on the available storage characteristics, refer to the counterpart of this section in the CREATE TABLE statement, PUT Clause (IDS). For a discussion of large-object characteristics, refer to Large-Object Data Types.

ADD CONSTRAINT Clause

Use the ADD CONSTRAINT clause to specify a constraint on a new or existing column or on a set of columns.

ADD CONSTRAINT Clause:
 
|--ADD CONSTRAINT----------------------------------------------->
 
                                           (1)
>--+-| Multiple-Column Constraint Format |----------------+-----|
   |    .-,------------------------------------------.    |
   |    V                                       (1)  |    |
   '-(----| Multiple-Column Constraint Format |------+--)-'

Notes:

See page Multiple-Column Constraint Format

For example, to add a unique constraint to the fname and lname columns of the customer table, use the following statement:

ALTER TABLE customer ADD CONSTRAINT UNIQUE (lname, fname)

To declare a name for the constraint, change the preceding statement:

ALTER TABLE customer 
   ADD CONSTRAINT UNIQUE (lname, fname) CONSTRAINT u_cust

When you do not specify a name for a new constraint, the database server provides one. You can find the name of the constraint in the sysconstraints system catalog table. For more information about the sysconstraints system catalog table, see the IBM Informix Guide to SQL: Reference.

When you add a constraint, the collating order must be the same as when the table was created.

Multiple-Column Constraint Format

Use the Multiple-Column Constraint Format option to assign a constraint to one column or a set of columns.

Multiple-Column Constraint Format:
 
                             .-,-------------.
                             V  (2)          |
|--+-+-UNIQUE----------+--(-----------column-+--)---------------------+-->
   | |  (1)            |                                              |
   | +--------DISTINCT-+                                              |
   | '-PRIMARY KEY-----'                                              |
   |                  (3)                                             |
   +-| CHECK Clause |-------------------------------------------------+
   |                 .-,-------------.                                |
   |                 V  (2)          |                           (4)  |
   '-FOREIGN KEY--(-----------column-+--)--| REFERENCES Clause |------'
 
>--+---------------------------------------+--------------------|
   |  (1)                             (5)  |
   '--------| Constraint Definition |------'

Notes:

Informix extension
Use path no more than 16 times
See page CHECK Clause
See page REFERENCES Clause
See page Constraint Definition

Element	Description	Restrictions	Syntax
column	A column on which the constraint is placed	No more than 16 columns	Identifier, p. Identifier

A multiple-column constraint has these restrictions:

It can include no more than 16 column names.
In Dynamic Server, the total length of the list of columns cannot exceed 390 bytes.
In Extended Parallel Server, the total length of the list of columns cannot exceed 255 bytes.

You can declare a name for the constraint and set its mode by means of Constraint Definition.