Datometry Documentation

You can specify override_table_uniqueness as a global setting in the Hyper-Q configuration file (dtm.ini). This example specifies ignore_all which overrides both SET and UPI tables.

"gateway".override_table_uniqueness=ignore_all

To learn more about specifying a hint as a gateway option in the Hyper-Q configuration file, see Gateway Configuration Parameters.

You can specify override_table_uniqueness=ignore_set as a query hint. This example specifies that the hint be used to to override SET tables. In the example shown, a second INSERT statement is able to successfully update the table because duplicate rows are not prohibited due to the SET table override. If the behavior of the SET table were being observed, the second ISERT statement would generate an error as duplicate rows would not be allowed.

CREATE SET TABLE table_name (
-- @dtm_override_table_uniqueness=ignore_set
      idnum integer,
      phone integer,
      name varchar(10));
INSERT INTO table_name (1,2,'hello');
INSERT INTO table_name (1,2,'hello');  

To learn more about Hyper-Q SQL hints, and how to use them within a SQL query, see About Hyper-Q SQL Hints.

This example overrides UPI tables by specifying override_table_uniqueness=ignore_upi. The query produces the duplicate row error DTM9060: Invalid update statement: 'Duplicate row error' with the attempted update as it is a SET table.

CREATE SET TABLE table_name (
-- @dtm_override_table_uniqueness=ignore_upi
      idnum integer,
      phone integer,
      name varchar(10));
INSERT INTO table_name (1,2,'hello');
INSERT INTO table_name (1,3,'hello');   
UPDATE table_name SET phone=2 where idnum=1;
-- The UPDATE query will error with DTM9060: Invalid update statement: 'Duplicate row error'

This example overrides both SET and UPI tables. As both SET and UPI constraints are ignored, the update to the table performs without error.

CREATE SET TABLE table_name (
-- @dtm_override_table_uniqueness=ignore_all
      idnum integer,
      phone integer,
      name varchar(10));
INSERT INTO table_name (1,2,'hello');
INSERT INTO table_name (1,3,'hello');   
UPDATE table_name SET phone=2 where idnum=1;