What is a SQL Create Table Statement?
A relational database schema is made up from a related set of objects that include tables, views, and indexes. The database is usually made up of user-created objects and system objects that are used to catalog user objects and manage the database instance. Many databases, like the Actian Data Platform, have a single schema for all applications, while others, like DB2, have tablespaces or DB Spaces to hold user schemas. The CREATE statement is a form of DML (Database Manipulation Language) statement, a subset of the SQL language specification used to create database objects.
The CREATE TABLE statement is used to create a new table in a relational database. The syntax often has non-standard extensions to specify storage criteria.
SQL Create Table Example
Below is a simple SQL create table example that creates a table to hold data about departments in a business:
CREATE TABLE DEPARTMENTS ( Department-ID int, Department-Name varchar(255), Department-Cost-Center int );
You can create tables using another table as a template. For example, the table below only contains rows that relate to Department-ID 10:
CREATE TABLE DEPARTMENT-10 AS SELECT * FROM DEPARTMENTS WHERE Department-ID = 10;
Actian SQL Examples
The following are specific to Actian databases.
This statement has the following format:
CREATE TABLE [IF NOT EXISTS] [schema.]table_name
(column_specification {, column_specification })
[table_constraint {, table_constraint}]
[with_clause]
table_name
Defines the name of the new table. It must be a valid object name.
column_specification
Defines the characteristics of the column.
table_constraint
Specifies the table-level constraint as described in Table-level and Column-level Constraints.
with_clause
Specifies WITH clause options separated by a comma. For more information, see WITH Clause.
Column Specification
The column specification in a CREATE TABLE statement defines the characteristics of a column in the table.
The column_specification has the following format:
column_name datatype
[[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT] [WITH NULL | NOT NULL]
[MASKED [AS {BASIC | NULL | 0 | ‘ ‘ }]
[GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
| GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]]
[[CONSTRAINT constraint_name] column_constraint
{ [CONSTRAINT constraint_name] column_constraint}]
column_name
Assigns a valid name (see Object Naming Rules) to the column.
datatype
Assigns a valid data type to the column. If CREATE TABLE…AS SELECT is specified, the new table takes its column names and formats from the results of the SELECT clause of the subselect specified in the AS clause (unless different column names are specified).
Note: For char and varchar columns, the column specification is in the number of bytes (not the number of characters).
DEFAULT clause
Specifies whether the column is mandatory.
WITH NULL | NOT NULL
Specifies whether the column accepts nulls:
WITH NULL
(Default) Indicates that the column accepts nulls. If the user supplies no value, null is inserted.
NOT NULL
Indicates that the column does not accept nulls.
[MASKED [AS {BASIC | NULL | 0 | ‘ ‘ }]
Displays the column with the specified mask characteristic unless the user has the UNMASK privilege. The mask characteristic defines how to display the masked data:
BASIC – Fills the width of the column with asterisks
NULL – NULL
0 – 0
‘ ‘ – blank
GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)] | GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates the column is an IDENTITY Columns. The column must be defined as integer or bigint.
[CONSTRAINT constraint_name] column_constraint
Specifies checks to be performed on the contents of the column to ensure appropriate data values.
DEFAULT Clause
The WITH|NOT DEFAULT clause in the column specification specifies whether a column requires an entry.
This clause has the following format:
[WITH] DEFAULT default_spec | WITH DEFAULT | NOT DEFAULT
[WITH] DEFAULT default_spec
Indicates that if no value is provided (because none is required), Actian Data Platform inserts the default value. The default value must be compatible with the data type of the column.
For character columns, valid default values include the constants: USER, CURRENT_USER, and SYSTEM_USER.
For boolean columns, valid default values include FALSE or TRUE.
WITH DEFAULT
Indicates that if no value is provided, Actian Data Platform inserts 0 for numeric and money columns, an empty string for character columns, the current date for ANSI date columns, and the current timestamp for timestamp columns.
NOT DEFAULT
Indicates the column is mandatory (requires an entry).
The following is an example of using the DEFAULT clause:
CREATE TABLE DEPT(dname CHAR(10), location CHAR(10) DEFAULT 'NY', creation DATE DEFAULT '01/01/20', budget MONEY DEFAULT 10000);
An identity column is an integer or bigint column whose values are automatically generated from a system-defined sequence.
An identity column is a way to automatically generate a unique numeric value for each row in a table. A table can have only one column that is defined with the identity attribute.
The IDENTITY clause has the following format:
[GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
| GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]]
where:
GENERATED ALWAYS AS [seq_name] IDENTITY [(seq_options)]
Indicates that the column value is determined by the corresponding sequence. The user cannot specify an explicit value for the column in an INSERT or UPDATE statement.
INSERT statements that contain ALWAYS identity columns in their column list must specify DEFAULT as the corresponding value. To override this behavior, use the OVERRIDING SYSTEM VALUE and OVERRIDING USER VALUE clauses of the INSERT statement.
The data type of the sequence matches the data type of the identity column.
GENERATED BY DEFAULT AS [seq_name] IDENTITY [(seq_options)]
Indicates that the user can optionally provide an explicit value for the column.
seq_name
Defines the name of the sequence.
seq_options
Control how the sequence supplies data when requested by an application. Sequence options can be specified in any order, and none are required.
Any of the following seq_options can be specified in a blank-space separated list:
START WITH number
Specifies the start of the sequence as an integer constant. The default value is 1 for positive sequences (positive increment) and -1 for negative sequences (negative increment). (This option is valid with the CREATE SEQUENCE statement only.)
RESTART WITH number
Specifies a new start value for the sequence. (This option is valid with the ALTER SEQUENCE statement only.)
INCREMENT BY number
Specifies the increment value (positive or negative) that produces successive values of the sequence.
Default: 1
MAXVALUE number
NO MAXVALUE / NOMAXVALUE
Specifies that sequences can generate values with an upper bound equivalent to that of the data type chosen to hold the sequence (for example, 2**31-1 for integers).
MINVALUE number
Specifies the minimum value allowed for the sequence.
NO MINVALUE / NOMINVALUE
Specifies that sequences can generate values with a lower bound equivalent to that of the data type chosen to hold the sequence (for example, -2**31 for integers).
CACHE number
Specifies the number of sequence values held in server memory. When the supply of numbers is exhausted, Actian Data Platform requires a catalog access to acquire the next set.
Default: 20
NO CACHE / NOCACHE
Specifies that sequence values are not to be cached by the server. When this option is selected, a catalog access is required for each request for a sequence value. This can severely degrade application performance.
Default: CACHE 20 (when neither CACHE nor NOCACHE are specified), which ensures low catalog overhead
CYCLE
Specifies that the sequence restarts at the beginning value once it reaches the minimum value (negative increment) or maximum value (positive increment).
Default: NO CYCLE
NO CYCLE / NOCYCLE
Specifies that the sequence is not cycled when the last valid value is generated. An error is issued to the requesting transaction.
Default: SEQUENTIAL
The sequence created to manage identity column values is accessible by its generated name. The generated sequence, however, cannot be explicitly dropped; instead, the identity column or table must be dropped, or the ALTER USER … ALTER COLUMN … DROP IDENTITY statement must be used.
Constraints
To ensure that the contents of columns fulfill your database requirements, specify constraints.
Constraints can be specified for individual columns or for the entire table. For more information, see Table-level and Column-level Constraints.
The types of constraints are:
- Unique Constraint – Ensures that a value appears in a column only once. Unique constraints are specified using the UNIQUE option.
- Referential Constraint – Ensures that a value assigned to a column appears in a corresponding column in another table. Referential constraints are specified using the REFERENCES option.
- Primary Key Constraint – Declares one or more columns for use in referential constraints in other tables. Primary keys must be unique.
Table-level and Column-level Constraints
Constraints can be specified for groups of columns as part of the table definition (table-level constraints) or for individual columns as part of the column specification (column-level constraints).
The constraint has the following syntax:
[CONSTRAINT constraint_name] constraint
constraint_name
Defines a name for the constraint. If the name is omitted, Actian Data Platform assigns one. The constraint name is used when dropping the constraint using the ALTER TABLE statement.
Note: We recommend defining a name when creating a constraint; otherwise, system catalogs must be queried to determine the system-defined name.
constraint
Is either a table-level constraint (table_constraint) or a column-level constraint (column_constraint).
table_constraint is one or more of the following:
UNIQUE (column_name {, column_name})
PRIMARY KEY (column_name {, column_name})
REFERENCES [schema.]table_name [(column_name {, column_name})] [enforce_option] [referential_actions]
column_constraint is one or more of the following:
UNIQUE
PRIMARY KEY
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]table_name[(column_name)] [enforce_option] [referential_actions]
where:
enforce_option
Specifies whether constraints are enforced. Valid values are:
NOT ENFORCED
Does not enforce the constraint when it is defined or when the table is updated. The constraint is defined in the database catalogs. NOT ENFORCED constraints can be used to generate improved SQL statements or query plans.
Indexes are not created for NOT ENFORCED constraints.
ENFORCED
(Default) Enforces the constraint.
There are additional options to specify referential actions and partitioning which are described at the docs.actian.com website.
In Summary
Actian Zen provides both a SQL and No-SQL or Key-index based API to give developers a choice of access methods. Actian Zen is a DBMS that is designed with low administration. It’s time to make your data easy; visit our website to find out how.