Overview | Syntax | Examples

The CREATE TABLE statement declares a table in an Omnidex Environment File. Tables that are declared in an environment can then be indexed and retrieved using SQL statements. An Omnidex table can point to an underlying Oracle, SQL Server, MySQL or ODBC table or view. An Omnidex table can also point to one or more raw data files that match the column layout of the table.

The basic CREATE TABLE statement records information about the underlying table or raw data files in the Omnidex Environment Catalog, but it does not actually create an underlying relational table or raw data file. Normally, the relational table or view file is created using the relational database's tools. In the case of raw data files, the files are usually received from a data provider, and application or an ETL tool. The CREATE TABLE statement simply stores meta-data about the table in the Omnidex Environment File. Omnidex does support an exception to this rule for the creation of temporary tables.

The CREATE TABLE statement declares the characteristics of the table. At a minimum, each table has a name and instructions for accessing the physical data. Omnidex supports configuration settings for each table, including settings for delimited files, data caches and index maintenance. Omnidex also provides rollup tables which pre-aggregate another table in the database and automatically optimize queries against the table.

The CREATE TABLE statement specifies the columns for a table. At a minimum, each column has a name and a datatype; however, it can also have other characteristics as well. Omnidex allows a column to have a different name than the one used in the underlying relational database. Omnidex allows the declaration of column usages that describe the purpose of the column. Omnidex also allows expression-based columns that are the result of a SQL expression, even if it does not exist in the underlying database or data file.

The CREATE TABLE statement specifies the constraints for a table. The most common constraints are primary and foreign constraints, used to declare parent-child relationships between tables. Omnidex allows constraints to be declared on both relational tables and raw data files, allowing either to be described using a full relational model. Omnidex allows these constraints to be declared, but it does not verify or enforce these constraints.

Like most databases, Omnidex allows indexes to be declared using the CREATE INDEX statement. For convenience, Omnidex also allows indexes to be declared using the CREATE TABLE statement. In most applications, many (if not all) of the columns are indexed with Omnidex, so it is convenient to declare the indexing directly in the table specification.

Omnidex allows constraints and indexing to be declared alongside each column. With inlining, the constraint and indexing specification is provided as each column is declared. This is a convenience for simple installations where most constraints and indexes consist of a single column.

See also:

• CREATE ENVIRONMENT
• CREATE DATABASE
• CREATE INDEX
• UPDATE INDEXES

Articles:

• Indexing Strategies
• Database-specific issues
• Rollup Tables