Integration: Relational Databases

SQL Server

SQL Server Constraints and Indexes

SQL Server table constraints and indexes should be declared in the Omnidex Environment File. Omnidex uses primary and foreign key table constraints to understand the nature of the data and to optimize table joins. Declaring primary keys for a table also allows Omnidex to fulfill more queries solely in the indexes without having to access the database. Omnidex uses an understanding of the SQL Server indexes to insure that the queries it submits to SQL Server will be well optimized.

Declaring Constraints

The table declarations in the Omnidex Environment File should include primary and foreign key constraints that establish parent-child relationships between the tables. While Omnidex does not validate or enforce these constraints; they are heavily used when optimizing queries. Omnidex does not need to know about column constraints that exist in SQL Server, such as NULL, NOT NULL, CHECK, or DEFAULT constraints.

In most situations, the constraints declared in Omnidex should match those declared in the SQL Server database; however, administrators may alter these if they wish. At times, a parent-child relationship may exist between tables in SQL Server even though a constraint is not formally declared. These can be declared in Omnidex, allowing more advanced optimization of multi-table queries.

Omnidex requires that the datatype of a foreign key constraint match the datatype of the referencing constraint. If this is not the case within the SQL Server database, it may be possible to remap the datatype in Omnidex so that they do match. Consult the documentation on datatypes to see the options for remapping datatypes.

Omnidex is tolerant of violations of foreign key constraints, such as foreign keys for which there are no primary keys in the corresponding tables. Omnidex is not tolerant of violations of primary key constraints, as these can lead to incorrect results returned from queries.

The following examples show constraints being declared in SQL Server and Omnidex CREATE TABLE statements:

SQL Server CREATE TABLE Statement

create table STATES
       (STATE_CD                CHAR(2),
        DESCRIPTION             VARCHAR(31),
        STATE_NUM               CHAR(2),
        REGION_CD               CHAR(2),
        COUNTRY_CD              CHAR(2),
        TAX_RATE                FLOAT,
        constraint              STATES_STATE_CD_PK
                                primary key (STATE_CD),
        constraint              STATES_COUNTRY_CD_FK
                                foreign key (COUNTRY_CD)
                                references COUNTRIES(COUNTRY_CD));

Omnidex CREATE TABLE Statement

create table          "STATES"
 physical             "STATES"
 (
  "STATE"             CHARACTER(2)   physical "STATE_CD",
  "DESCRIPTION"       STRING(31),
  "STATE_CODE"        CHARACTER(2)   physical "STATE_NUM",
  "REGION"            CHARACTER(2)   physical "REGION_CD",
  "COUNTRY"           CHARACTER(2)   physical "COUNTRY_CD",
  "TAX_RATE"          FLOAT,
  constraint STATES_STATE_PK primary ("STATE"),
  constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
 )
 in                   "simple.xml";

Declaring Indexes

SQL Server indexes that exist on primary and foreign key constraints should be declared as NATIVE indexes in the Omnidex Environment File. It can be beneficial to declare other SQL Server indexes as well.

The Omnidex SQL Engine will evaluate the presence of native SQL Server indexes when deciding how to process a statement. At times, it may be faster to rely on the native SQL Server indexes than to use Omnidex indexes. This is especially true for searches on unique values, such as primary keys.

SQL Server indexes are declared in the Omnidex Environment File using the CREATE TABLE or CREATE INDEX statements. The following example shows native indexes being declared for a table.

Declaring Native Indexes

create table          "STATES"
 physical             "STATES"
 (
  "STATE"             CHARACTER(2)   physical "STATE_CD",
  "DESCRIPTION"       STRING(31),
  "STATE_CODE"        CHARACTER(2)   physical "STATE_NUM",
  "REGION"            CHARACTER(2)   physical "REGION_CD",
  "COUNTRY"           CHARACTER(2)   physical "COUNTRY_CD",
  "TAX_RATE"          FLOAT,
  constraint STATES_STATE_PK primary ("STATE"),
  constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES",
  native index STATE_IDX ("STATE"),
  native index COUNTRY_IDX ("COUNTRY")
 )
 in                   "simple.xml";

Additional Resources

See also:

 
Back to top
integration/rdbms/sqlserver/constraints.txt ยท Last modified: 2016/06/28 22:38 (external edit)