This tutorial describes how to create and drop collections and indexes in MongoDB with SQL statements.

Requirements

To create database objects, you must first connect to the server. For instructions, see Connecting to MongoDB.

General Information

In SQL mode, MongoDB collections are treated as tables, so database objects are created using Data Definition Language (DDL) statements. You can run them with the MongoDBCommand component or with any other tool that can run SQL queries.

Creating and Dropping Collections

The CREATE TABLE [IF NOT EXISTS] statement creates a MongoDB collection. The columns and their types are stored in the collection as a $jsonSchema validator (the declared type of a column is kept in the title of the property). Therefore, a new empty collection already has columns, and you can insert data into it immediately. All columns except _id and the columns declared NOT NULL accept NULL values.

CREATE TABLE IF NOT EXISTS customers (
  _id OID,
  name VARCHAR,
  city VARCHAR,
  created DATETIME
);

The DROP TABLE [IF EXISTS] statement removes the collection together with its data.

DROP TABLE customers;

CREATE TEMP TABLE creates a table that's local to the connection. The table isn't created on the server.

An INSERT statement can write a field that exists neither in the schema nor in any document of the collection, including an empty collection created by another tool, as long as the Allow Add Fields connection string parameter is true (the default). In a collection that has a schema, the server accepts or rejects such a document according to the Schema Validation parameter. The schema itself doesn't change.

Column Types

Use the column types described in Data Type Mapping: OID, VARCHAR, INT, INT64, DOUBLE, DECIMAL128, BOOLEAN, DATETIME, UUID, DATETIMEOFFSET, BLOB (also BINARY, VARBINARY) for binary data, and the MongoDB types BSONTIMESTAMP, REGEX, JAVASCRIPT, and JAVASCRIPTWITHSCOPE. You can also use common SQL type names: CHAR, NVARCHAR, TEXT, TINYINT, SMALLINT, SINGLE, FLOAT4, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, FLOAT8, BIT, BOOL, DATE, TIMESTAMP, GUID, and others. A value is stored as the nearest BSON type:

• TINYINT and SMALLINT – int
• INTEGER and BIGINT – long
• SINGLE, REAL, DECIMAL(p,s), and NUMERIC – double

TINYINT, SMALLINT, and SINGLE (FLOAT4) are read as declared: Byte, Int16, and Single, respectively. The other types are read according to the BSON type (for example, INTEGER is read as Int64, and DECIMAL as Double). If a value doesn't fit in the declared type (for example, it was written by another client), GetValue returns it as it's stored, while GetByte, GetInt16, and GetFloat raise InvalidCastException with a message such as The value 300 of the column "b" is not a Byte.

Schema Validation

The validator also stores the constraints of the declared type:

• maxLength for CHAR(n), NCHAR(n), VARCHAR(n), and NVARCHAR(n)
• A range for TINYINT, SMALLINT, and DECIMAL(p,s) (for DECIMAL, also a step of 10^-s)
• A required field for NOT NULL columns

The Schema Validation connection string parameter defines whether MongoDB checks documents against this schema. Off (the default) doesn't check the documents, Warn writes a warning to the server log for a document that doesn't match the schema, and Error rejects such documents for every client. To set the mode for a particular table, use the WITH clause of the CREATE TABLE statement. It overrides the connection string parameter.

CREATE TABLE products (_id OID, name VARCHAR, price DECIMAL128) WITH (Schema Validation = Error);

Creating and Dropping Indexes

The CREATE [UNIQUE] INDEX and DROP INDEX statements create and drop the indexes of a collection.

CREATE UNIQUE INDEX ix_customers_name ON customers (name);

DROP INDEX ix_customers_name;

Unsupported Statements

The following statements and clauses aren't supported. An attempt to use them raises a NotSupportedException with an explanatory message:

• ALTER TABLE
• Views and triggers
• CREATE TABLE ... AS SELECT
• DEFAULT and constraints like UNIQUE in CREATE TABLE
• Partial indexes (CREATE INDEX ... WHERE) and indexes on expressions

Executing the Statements from Code

The following example creates a collection and a unique index using MongoDBCommand.

using (var connection = new MongoDBConnection("Host=localhost;Database=sample;User Id=user;Password=pwd;License Key=***")) {
  connection.Open();
  var command = connection.CreateCommand();
  command.CommandText = "CREATE TABLE IF NOT EXISTS customers (_id OID, name VARCHAR, city VARCHAR, created DATETIME)";
  command.ExecuteNonQuery();
  command.CommandText = "CREATE UNIQUE INDEX ix_customers_name ON customers (name)";
  command.ExecuteNonQuery();
}