QUEL Reference Guide > QUEL Reference Guide > QUEL and EQUEL Statements > CREATE--Create a Table
Was this helpful?
CREATE--Create a Table
Valid in: QUEL, EQUEL
Creates a new database table.
This statement has the following format:
[##] create [locationname:]tablename 
              (columnname=format [null_clause] 
              {, columnname=format} [null_clause]) 
              [with     [location = locationname]
                            [no]journaling 
                            [no]duplicates]]
The create statement creates an empty table, owned by the user issuing the statement. The table is created as a heap. To change to a different storage structure, use the modify statement. The following table describes the create statement parameters:
tablename
Specifies the name of the table. Table names must not begin with "ii".
columnname
Specifies the name of each column in the new table. The column name must be a valid object name.
format
Specifies the data type, length, and null characteristics of each column. Format has the syntax datatype [not null [with | not default] | with null]
The optional with clause consists of a comma-separated list of any of the following parameters:
• location = locationname
• [no]journaling
• [no]duplicates
The optional null_clause enables you to set the location, journaling, and duplicate row characteristics of the table. The following summarizes the possible null and default settings of the with_clause and the resulting column attributes (the default is not null with default):
Null/Default Specification
Nulls Allowed?
Defaults Allowed?
None
No
Yes
with null
Yes
Yes (null is default)
not null
No
Yes
not null with default
No
Yes
not null not default (mandatory column)
No
No
A table can have a maximum of 1024 columns. A row can have a maximum of 1928 bytes. A text or varchar column uses two bytes in addition to the specified length (to store the string length). Nullable columns (columns defined with null) use an additional byte for a null flag. In tables having a compressed format (chash, cbtree, cheap, or cisam), c columns require 1 byte in addition to the declared length, and char columns require 2 additional bytes. These space requirements must be considered as part of the maximum allowable 1928 bytes per row.
In the optional with clause, locationname refers to the areas where the new table is created. The locations must be defined on the system, and the database must have been extended to the corresponding areas. If you do not specify a location, the default area for the database is assumed. If you specify multiple locations, the table is physically partitioned across the areas. For more information, see the Database Administrator Guide.
If you specify with journaling, all append, replace and delete statements that update this table are logged in the journal for this database, if journaling for the database is enabled. (To enable database journaling, use the ckpdb command.) Journaling allows the recovery system to reconstruct the table after a disk crash. You need not enable journaling to recover from operating system or server failures because this is handled by normal query processing. Journaling also allows an audit trail to be built for the table. You can use this audit trail to monitor updates to a table or maintain change histories.
If you specify with duplicates, the table accepts duplicate rows even if the table is subsequently modified to a storage structure which does not ordinarily permit duplicate rows. The default is with noduplicates. The duplicates|noduplicates parameter is irrelevant when the table is a heap. For more details, see MODIFY--Change Table or Index Properties.
Last modified date: 08/28/2024