SQL

SQL Create Table

what is cross join in SQL

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.