Specifying informational constraints on nicknames - examples

In the following remote table, the data in the salary column is always greater than 10000.

CREATE TABLE account.salary (
    empno INTEGER NOT NULL PRIMARY KEY,
    salary  INTEGER NOT NULL
);

Create a nickname for this table:

CREATE NICKNAME account.salary FOR myserv.account.salary;

Then add informational check constraints for the nickname by issuing the following statement:

ALTER NICKNAME account.salary ADD CONSTRAINT cons1 CHECK( salary > 10000 )
NOT ENFORCED
ENABLE QUERY OPTIMIZATION;

In this example, there are two nicknames N1 and N2. Column F1 of nickname N2 contains the key value in column P1 of nickname N1. You can define the referential constraint on nickname N2 by issuing the following statement:

ALTER NICKNAME SCHEMA1.N2 ADD CONSTRAINT ref1 
                         FOREIGN KEY (F1) REFERENCES SCHEMA1.N1 (P1)
                         NOT ENFORCED;

In this example, nickname N3 with column F1 contains the key value in column P1 of table T1. You can define the referential constraint on nickname N3 by issuing the following statement:

ALTER NICKNAME SCHEMA1.N3 ADD CONSTRAINT ref1 
                         FOREIGN KEY (F1) REFERENCES SCHEMA1.T1 (P1)
                         NOT ENFORCED;

In this example, table T2 with column F1 contains the key value in column P1 of nickname N4. You can define the referential constraint on table T2 by issuing the following statement:

ALTER TABLE SCHEMA1.T2 ADD CONSTRAINT ref1 
                         FOREIGN KEY (F1) REFERENCES SCHEMA1.N4 (P1)
                         NOT ENFORCED;

In this example, the column pair C1 and C2 uniquely determine the value in the column P1. You can define the functional dependency by issuing the following statement:

ALTER NICKNAME SCHEMA1.NICK1 ADD CONSTRAINT FD1 CHECK( P1 DETERMINED BY (C1,C2) )
                        NOT ENFORCED ENABLE QUERY OPTIMIZATION;

This statement defines a primary key for a table-structured file:

CREATE NICKNAME MY_FILE (
       X INTEGER NOT NULL,
       Y INTEGER,
       PRIMARY KEY (X) NOT ENFORCED
)  FOR SERVER MY_SERVER OPTIONS(FILE_PATH '/usr/pat/DRUGDATA1.TXT'); 

The statement creates four dimension tables and one fact table:

CREATE TABLE SCHEMA.FACT (
       LOCATION_CODE   INTEGER NOT NULL,
       PRODUCT_CODE    INTEGER NOT NULL,             
       CUSTOMER_CODE   INTEGER NOT NULL,
       SDATE           DATE NOT NULL,
       SALES           INTEGER NOT NULL
);

CREATE TABLE SCHEMA.LOCATION (
       LOCATION_CODE   INTEGER NOT NULL PRIMARY KEY,
       STATE           CHAR(2) NOT NULL,
       SHOP_ID         INTEGER NOT NULL,
       ...
);

CREATE TABLE SCHEMA.PRODUCT (
       PRODUCT_CODE    INTEGER NOT NULL PRIMARY KEY,
       PRODUCT_CAT     INTEGER NOT NULL,
       PRODUCT_NAME    VARCHAR(20) NOT NULL,
       ...
);

CREATE TABLE SCHEMA.CUSTOMER (
       CUSTOMER_CODE   INTEGER NOT NULL PRIMARY KEY,
       NAME            VARCHAR(20) NOT NULL,
       TEL             VARCHAR(10) NOT NULL,
       ...
);

CREATE TABLE SCHEMA.TIMEDIM (
       SDATE           DATE NOT NULL UNIQUE,
       YEAR            INTEGER NOT NULL,
       QUARTER         INTEGER NOT NULL,
       ...
);

The federated server creates the following nicknames for the fact table and the dimension tables:

CREATE NICKNAME SCHEMA.FACT FOR SERVER.SCHEMA.FACT;
CREATE NICKNAME SCHEMA.LOCATION FOR SERVER.SCHEMA.LOCATION;
CREATE NICKNAME SCHEMA.PRODUCT FOR SERVER.SCHEMA.PRODUCT;
CREATE NICKNAME SCHEMA.CUSTOMER FOR SERVER.SCHEMA.CUSTOMER;
CREATE NICKNAME SCHEMA.TIMEDIM FOR SERVER.SCHEMA.TIMEDIM;

You can define the following foreign key relationship by issuing the following statements:

ALTER NICKNAME SCHEMA.FACT ADD CONSTRAINT L1 FOREIGN KEY (LOCATION_CODE)
                               REFERENCES SCHEMA.LOCATION(LOCATION_CODE) 
                               NOT ENFORCED ENABLE QUERY OPTIMIZATION;

ALTER NICKNAME SCHEMA.FACT ADD CONSTRAINT P1 FOREIGN KEY (PRODUCT_CODE)
                               REFERENCES SCHEMA.PRODUCT(PRODUCT_CODE) 
                               NOT ENFORCED ENABLE QUERY OPTIMIZATION;

ALTER NICKNAME SCHEMA.FACT ADD CONSTRAINT C1 FOREIGN KEY (CUSTOMER_CODE)
                               REFERENCES SCHEMA.CUSTOMER(CUSTOMER_CODE) 
                               NOT ENFORCED ENABLE QUERY OPTIMIZATION;

ALTER NICKNAME SCHEMA.FACT ADD CONSTRAINT S1 FOREIGN KEY (SDATE)
                               REFERENCES SCHEMA.TIMEDIM(SDATE) 
                               NOT ENFORCED ENABLE QUERY OPTIMIZATION;

When the value of the TEL column in the CUSTOMER nickname is unique, you can add the following informational unique constraint with this statement:

ALTER NICKNAME SCHEMA.CUSTOMER ADD CONSTRAINT U1 UNIQUE( TEL ) 
                                   NOT ENFORCED ENABLE QUERY OPTIMIZATION;

When the value of the SHOP_ID column in the LOCATION nickname uniquely determines the value of the LOCATION_ID column, you can define the following functional dependency with this statement:

ALTER NICKNAME SCHEMA.LOCATION 
ADD CONSTRAINT F1 CHECK( LOCATION_ID DETERMINED BY SHOP_ID )
                        NOT ENFORCED ENABLE QUERY OPTIMIZATION;

Because the value of the QUARTER column in the TIMEDIM nickname is between 1 and 4, you can define the following informational check constraint with this statement:

ALTER NICKNAME SCHEMA.TIMEDIM 
ADD CONSTRAINT Q1 CHECK( QUARTER BETWEEN 1 AND 4 )
                        NOT ENFORCED ENABLE QUERY OPTIMIZATION;

The statements in this example create nicknames for remote tables. Nicknames have primary keys when remote tables have primary keys. When you create nickname on views, nicknames lack primary keys. In this case, you can change the nickname to add an informational primary key constraint. For example:

CREATE NICKNAME SCHEMA.LOCATION FOR SERVER.SH.V_LOCATION;
ALTER NICKNAME SCHEMA.LOCATION 
      ADD CONSTRAINT P1 PRIMARY KEY ( LOCATION_CODE ) NOT ENFORCED;