SQL Create Table

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 Avalanche SQL Create Table 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), Avalanche 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, Avalanche 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.

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, Avalanche 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.

Specifying Constraints within a SQL CREATE TABLE statement

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.

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, Avalanche 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 under the SQL Language section.

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 Home Page to find out how.