This shows you the differences between two versions of the page.
|
integration:rdbms:mysql:constraints [2011/04/06 16:12] doc |
integration:rdbms:mysql:constraints [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Integration: Relational Databases ====== | ||
| - | |||
| - | ===== MySQL ===== | ||
| - | |||
| - | [[integration:rdbms:mysql:home|Overview]] | | ||
| - | [[integration:rdbms:mysql:environments|Environments]] | | ||
| - | [[integration:rdbms:mysql:databases|Databases]] | | ||
| - | [[integration:rdbms:mysql:tables|Tables]] | | ||
| - | **[[integration:rdbms:mysql:constraints|Constraints]]** | | ||
| - | [[integration:rdbms:mysql:datatypes|Datatypes]] | | ||
| - | [[integration:rdbms:mysql:queries|Queries]] | | ||
| - | [[integration:rdbms:mysql:updates|Updates]] | | ||
| - | [[integration:rdbms:mysql:example|Example]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | ==== MySQL Constraints and Indexes ==== | ||
| - | |||
| - | MySQL table constraints and indexes should be declared in the Omnidex Environment File. Omnidex will consider constraints and indexes when optimizing queries, and these are critical to some of the advanced optimization techniques in Omnidex. | ||
| - | |||
| - | === 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. | ||
| - | |||
| - | In most situations, the constraints declared in Omnidex should match those declared in the MySQL database; however, administrators may alter these if they wish. At times, a parent-child relationship may exist between tables in MySQL 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 MySQL datatype, it may be possible to remap the datatype in Omnidex so that they do match. Consult the documentation on [[integration:rdbms:mysql:datatypes|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 MySQL and Omnidex CREATE TABLE statements: | ||
| - | |||
| - | **MySQL CREATE TABLE Statement** | ||
| - | |||
| - | <code> | ||
| - | create table STATES | ||
| - | (STATE_CD CHAR(2), | ||
| - | DESCRIPTION VARCHAR(31), | ||
| - | STATE_NUM CHAR(2), | ||
| - | REGION_CD CHAR(2), | ||
| - | COUNTRY_CD CHAR(2), | ||
| - | TAX_RATE DOUBLE, | ||
| - | constraint STATES_STATE_CD_PK | ||
| - | primary key (STATE_CD), | ||
| - | constraint STATES_COUNTRY_CD_FK | ||
| - | foreign key (COUNTRY_CD) | ||
| - | references COUNTRIES(COUNTRY_CD)); | ||
| - | </code> | ||
| - | |||
| - | **Omnidex CREATE TABLE Statement** | ||
| - | <code> | ||
| - | 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"; | ||
| - | </code> | ||
| - | |||
| - | === Declaring Indexes === | ||
| - | |||
| - | MySQL indexes that exist on primary and foreign key constraints should be declared as NATIVE indexes in the Omnidex Environment File. This is true whether the MySQL index was created using MySQL's CREATE INDEX statement or whether the MySQL constraint was created using the "USING INDEX" clause. It can also be beneficial to declare other MySQL indexes as well. | ||
| - | |||
| - | The Omnidex SQL Engine will evaluate the presence of native MySQL indexes when deciding how to process a statement. At times, it may be faster to rely on the native MySQL indexes than to use Omnidex indexes. This is especially true for searches on unique values, such as primary keys. | ||
| - | |||
| - | MySQL indexes are declared in the Omnidex Environment File using the [[dev:sql:statements:create_table:home|CREATE TABLE]] or [[dev:sql:statements:create_index:home|CREATE INDEX]] statements. The following example shows | ||
| - | native indexes being declared for a table. | ||
| - | |||
| - | <code> | ||
| - | 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"; | ||
| - | </code> | ||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[integration:rdbms:mysql:tables|Prev]]** | | ||
| - | **[[integration:rdbms:mysql:datatypes|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:integration:rdbms:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||