Create Table Statements

A create table statement is used to... create a table that will hold data.

When you create a table, you can specify:

  • The columns of the table and their types
  • The constraints applied to individual columns
  • The constraints applied to the table as a whole

Create table statements come in two forms.

create-table

CREATE TEMP TEMPORARY TABLE object-nameobject-name AS select-stmtselect-stmt ( column-defcolumn-def table-constrainttable-constraint , )

Create as select

The simplest form is to just dump the output of a select-stmt into a new table. This is mostly used with temporary tables, as there is no way to specify constraints on the resulting table. On the T-SQL backend, this translates to a SELECT ... INTO tbl FROM ....

create temp table MyExampleTable as
    select * from AnotherTable a
    join YetAnotherTable y on a.SomeColumn = y.SomeColumn

Create with column definitions

The more common form is to specify the column names, types, and constraints for the table. This is what you use to define your schema.

Each column is defined with a column-def.

column-def

namename type-nametype-name NULL COLLATE namename DEFAULT exprexpr column-constraintcolumn-constraint

This is often just a column-name and a type-name, but can optionally include column-constraints.

column-constraint

CONSTRAINT namename UNIQUE PRIMARY KEY ASC DESC AUTOINCREMENT foreign-key-clauseforeign-key-clause

A foreign-key-clause is a valid column constraint. This gets its own syntax diagram since it is also referenced by table-constraint.

foreign-key-clause

REFERENCES object-nameobject-name ( namename , ) ON DELETE SET NULL SET DEFAULT CASCADE RESTRICT NO ACTION

The supported constraints are:

  • UNIQUE: This column is unique across the table.

  • PRIMARY KEY: This column is the primary key of the table. If AUTOINCREMENT is specified, the column will also have a default value generated by the database when new rows are inserted.

  • REFERENCES: This column is a foreign key referencing a column in another table.

Table constraints

table-constraint

After specifying the columns of the table, you can also define some table-wide constraints that apply to more than one column. These are table-constraints.

CONSTRAINT namename FOREIGN KEY ( namename , ) foreign-key-clauseforeign-key-clause CHECK ( exprexpr ) UNIQUE PRIMARY KEY ( namename ASC DESC , )

Some of these are just multi-column versions of the column constraints and work the same. The CHECK constraint is table-wide and should be a boolean expression, which will be checked when rows in the table are inserted or updated.

Constraint naming

Constraints can be optionally named, but if no name is provided, one will be generated automatically according to the table name, constraint type, and related column.

Note that the default generated name for CHECK constraints will collide if you have multiple CHECK constraints on one table. In this case you should explicitly name those constraints.

results matching ""

    No results matching ""