SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-89
Considerations for CREATE TABLE
Reduced Space Requirements for NOT DROPPABLE
Constraints
Using the NOT DROPPABLE option on a NOT NULL constraint reduces the space
required for the table. A column that allows nulls—or that might allow nulls at some
later time—uses two extra bytes in each row to store the null indicator. If you specify
that the NOT NULL constraint is NOT DROPPABLE, NonStop SQL/MX creates the
table without these extra bytes.
Using the NOT DROPPABLE option on a PRIMARY KEY or using STORE BY
PRIMARY KEY reduces the space required for the table and eliminates the need to
create an index for accessing the table by primary key.
Constraints Implemented With Indexes
NonStop SQL/MX uses indexes to implement all UNIQUE constraints, the foreign key
portion of all referential constraints, and any PRIMARY KEY constraints that are not
enforced by the clustering key. Necessary indexes are automatically created when you
create a table with these constraints. If you add a constraint to an existing table,
NonStop SQL/MX checks if an existing index can be used to implement the constraint,
creating a new index (if possible, with the same name as the constraint) if needed.
For small tables, you need not be concerned about the details of this mechanism. For
large tables, however, the indexes used to enforce constraints can require significant
amounts of disk space. You might prefer to create and partition constraint-supporting
indexes directly so that you can control the use of disk space or so that you can specify
indexes that provide more effective access paths for your application than those
created by default to support constraints.
To create constraint-supporting indexes directly, use CREATE TABLE to create the
table without index-implemented constraints, use CREATE INDEX to create
appropriate indexes, and then use ALTER TABLE ... ADD CONSTRAINT to add
constraints to the table.
Limits for Tables
The size of a row of a table is limited to 4040 bytes. The executor reserves 4 bytes for
a header, leaving 4036 bytes for the sum of all the column sizes (including the
SYSKEY column, if the table has a SYSKEY). A SYSKEY has a size of 8 bytes. The
number of bytes required to store a column depends on the data type of that column. If
the column does not have the NOT NULL attribute, NonStop SQL/MX uses an
additional 2 bytes for the NULL indicator for that column. Each variable-length
character column uses an additional 8 bytes for the column length.
Tables and Triggers
If you plan to create triggers on a table, its primary key length cannot exceed 239
bytes. A table which will not have triggers can have a primary key of 255 bytes. For
information about this limit, see Triggers and Primary Keys on page 2-101.