4D Doc Center : CREATE TABLE

Home

4D v14

CREATE TABLE



	Command CREATE TABLCREATE TABLE [IF NOT EXISTS] [sql_name.]sql_name({column_definition \|table_constraint}[PRIMARY KEY], ... , {column_definition \|table_constraint}[PRIMARY KEY]) [{ENABLE \| DISABLE} REPLICATE]

Description

The CREATE TABLE command is used to create a table named sql_name having the fields specified by passing one or more column_definition and/or table_constraint type arguments. If the IF NOT EXISTS constraint is passed, the table is only created when there is no table with the same name already in the database. Otherwise, it is not created and no error is generated.

The first sql_name parameter (optional) can be used to designate the SQL schema to which you want to assign the table. If you do not pass this parameter or if you pass the name of a schema that does not exist, the table is automatically assigned to the default schema, named "DEFAULT_SCHEMA." For more information about SQL schemas, please refer to the Principles for integrating 4D and the 4D SQL engine section.

Note: It is also possible to assign a table to an SQL schema using the "Schemas" pop-up menu found in the 4D table Inspector palette. This menu contains the list of schemas defined in the database.

A column_definition contains the name (sql_name) and data type (sql_data_type_name) of a column and a table_constraint restricts the values that a table can store.

The PRIMARY KEY keyword is used to specify the primary key when the table is created. For more information about primary keys, please refer to the Principles for integrating 4D and the 4D SQL engine section.

The ENABLE REPLICATE and DISABLE REPLICATE keywords are used to enable or disable the mechanism allowing replication of the table (see the Replication via SQL section).

Example 1

Here is a simple example for creating a table with two columns:

CREATE TABLE ACTOR_FANS
(ID INT32, Name VARCHAR);

Example 2

This example creates the same table but only if there is no existing table with the same name:

CREATE TABLE IF NOT EXISTS ACTOR_FANS
(ID INT32, Name VARCHAR);

Example 3

This example creates a "Preferences" table and assigns it to the "Control" schema:

CREATE TABLE Control.Preferences
(ID INT32, Value VARCHAR);