>>obey TEST020(tests);
>>create table  test020t1 (c1 int not null primary key,
+>                      c2 char(20) default 't20t1''s column c2');

--- SQL operation complete.
>>invoke test020t1;

-- Definition of Trafodion table TRAFODION.SCH.TEST020T1
-- Definition current  Thu Jan 19 17:38:24 2017

  (
    C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C2                               CHAR(20) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT _ISO88591't20t1''s column c2'
  )
  PRIMARY KEY (C1 ASC)

--- SQL operation complete.
>>showddl test020t1;

CREATE TABLE TRAFODION.SCH.TEST020T1
  (
    C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C2                               CHAR(20) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT _ISO88591't20t1''s column c2'
  , PRIMARY KEY (C1 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>insert into test020t1(c1) values (1), (3), (2);

--- 3 row(s) inserted.
>>select * from test020t1;

C1           C2                  
-----------  --------------------

          1  t20t1's column c2   
          2  t20t1's column c2   
          3  t20t1's column c2   

--- 3 row(s) selected.
>>
>>create table  test020t2 (c char(15) not null, d int not null,
+>                      primary key (c, d));

--- SQL operation complete.
>>invoke test020t2;

-- Definition of Trafodion table TRAFODION.SCH.TEST020T2
-- Definition current  Thu Jan 19 17:38:28 2017

  (
    C                                CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , D                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (C ASC, D ASC)

--- SQL operation complete.
>>showddl test020t2;

CREATE TABLE TRAFODION.SCH.TEST020T2
  (
    C                                CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , D                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (C ASC, D ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>insert into test020t2 values ('Donald''s', 1),
+>                          ('Minie''s', 5),
+>                          ('zebra"', 23);

--- 3 row(s) inserted.
>>select * from test020t2;

C                D          
---------------  -----------

Donald's                   1
Minie's                    5
zebra"                    23

--- 3 row(s) selected.
>>
>>-- duplicate partitioning key values
>>create table test020t3 (test020t3_c1 time not null primary key);

--- SQL operation complete.
>>
>>create table test020t4 (test020t4_c1 real not null primary key);

--- SQL operation complete.
>>
>>--------------------------------------------
>>-- test cases for solution 10-030507-6228 --
>>--------------------------------------------
>>
>>-- The following Create Table statement should execute successfully
>>create table test020t31 (g0 smallint default 32767 not null not droppable,
+>  primary key (g0) not droppable);

--- SQL operation complete.
>>insert into test020t31 default values;

--- 1 row(s) inserted.
>>select * from test020t31;

G0    
------

 32767

--- 1 row(s) selected.
>>-- The following Drop Table statement should execute successfully
>>drop table test020t31;

--- SQL operation complete.
>>
>>-- The following Create Table statement should fail
>>create table test020t31 (g0 smallint no default primary key);

*** ERROR[1135] Clustering key column G0 must be assigned a NOT NULL NOT DROPPABLE constraint.

--- SQL operation failed with errors.
>>drop table if exists test020t31;

--- SQL operation complete.
>>
>>-- The following Create Table statement should fail
>>create table test020t31 (g0 smallint default 32768 not null primary key);

*** ERROR[8411] A numeric overflow occurred during an arithmetic computation or data conversion. Source Type:INTEGER SIGNED(MBIN32S) Source Value:32768 Target Type:LARGEINT(IBIN64S) Max Target Value:32767. Instruction:RANGE_HIGH_S32S64 Operation:RANGE_HIGH.

*** ERROR[1186] Column G0 is of type SMALLINT SIGNED which is not compatible with the default value's type, 32768.

--- SQL operation failed with errors.
>>
>>--
>>-- Test columns named SYSKEY
>>--
>>
>>-- Expecting error 1269 message: Col name SYSKEY is reserved.
>>create table test020t5(syskey int);

*** ERROR[1269] Column name SYSKEY is reserved for internal system usage. It cannot be specified as a user column.

--- SQL operation failed with errors.
>>
>>cqd traf_allow_reserved_colnames 'ON';

--- SQL operation complete.
>>create table test020t5(c int not null not droppable primary key not droppable,
+>                    syskey char(4));

--- SQL operation complete.
>>invoke test020t5;

-- Definition of Trafodion table TRAFODION.SCH.TEST020T5
-- Definition current  Thu Jan 19 17:38:51 2017

  (
    C                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SYSKEY                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  )
  PRIMARY KEY (C ASC)

--- SQL operation complete.
>>showddl test020t5;

CREATE TABLE TRAFODION.SCH.TEST020T5
  (
    C                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SYSKEY                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (C ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>insert into test020t5 values (1, 'abcd'), (2, 'efgh');

--- 2 row(s) inserted.
>>select * from test020t5;

C            SYSKEY
-----------  ------

          1  abcd  
          2  efgh  

--- 2 row(s) selected.
>>alter table test020t5 add constraint test020t5c1 check (syskey >= 'aaaa');

--- SQL operation complete.
>>showddl test020t5;

CREATE TABLE TRAFODION.SCH.TEST020T5
  (
    C                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , SYSKEY                           CHAR(4) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  , PRIMARY KEY (C ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

ALTER TABLE TRAFODION.SCH.TEST020T5 ADD CONSTRAINT TRAFODION.SCH.TEST020T5C1
  CHECK (TRAFODION.SCH.TEST020T5.SYSKEY >= 'aaaa')

--- SQL operation complete.
>>-- Expecting an error message when the following insert statement is executed.
>>insert into test020t5 values (3, '1234');

*** ERROR[8101] The operation is prevented by check constraint TRAFODION.SCH.TEST020T5C1 on table TRAFODION.SCH.TEST020T5.

--- 0 row(s) inserted.
>>
>>cqd traf_allow_reserved_colnames reset;

--- SQL operation complete.
>>
>>--------------------------------------------
>>-- test cases for solution 10-040607-6721 --
>>--------------------------------------------
>>-- Expect success when an index is created specifying only a user-specified SYSKEY.
>>create index test020t5_ix1 on test020t5 (syskey);

--- SQL operation complete.
>>
>>-- Expect success when index column list has a user-specified SYSKEY in any position.
>>alter table test020t5 add column d int;

--- SQL operation complete.
>>create index test020t5_i1 on test020t5 (d, syskey);

--- SQL operation complete.
>>create index test020t5_i2 on test020t5 (syskey, d);

--- SQL operation complete.
>>
>>-- Expect success when a key value is specified for a user-specified SYSKEY.
>>create index test020t5_i3 on test020t5 (c, syskey);

--- SQL operation complete.
>>
>>-- Expect success when a partition is added that specifies a value for a user-specified SYSKEY.
>>insert into test020t5(c,syskey,d) values (350,'efgh',375);

--- 1 row(s) inserted.
>>
>>-- system-generated column SYSKEY
>>create table test020t6(c int);

--- SQL operation complete.
>>invoke test020t6;

-- Definition of Trafodion table TRAFODION.SCH.TEST020T6
-- Definition current  Thu Jan 19 17:40:10 2017

  (
    SYSKEY                           LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , C                                INT DEFAULT NULL
  )

--- SQL operation complete.
>>showddl test020t6;

CREATE TABLE TRAFODION.SCH.TEST020T6
  (
    C                                INT DEFAULT NULL
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>-- Expecting error 1139 message:
>>--   System-generated column SYSKEY of base table TEST020T6C1 cannot
>>--   appear in the search condition of a Check constraint definition.
>>-- when the following alter statement is executed.
>>alter table test020t6 add constraint test020t6c1 check (syskey >= 0);

*** ERROR[1139] System-generated column SYSKEY of base table TRAFODION.SCH.TEST020T6 cannot appear in the search condition of a check constraint definition.

--- SQL operation failed with errors.
>>
>>--------------------------------------------
>>-- test cases for solution 10-040607-6721 --
>>--------------------------------------------
>>-- Expecting error 1112:
>>-- An index column list cannot consist only of the system-generated SYSKEY.
>>create index test020t6_ix1 on test020t6 (syskey);

*** ERROR[1112] An index column list cannot consist only of the system-generated column SYSKEY.

--- SQL operation failed with errors.
>>
>>-- Expect success when index column list has the system-generated SYSKEY in the last position,
>>-- but failure with error 1089 if the system-generated SYSKEY is not in the last position.
>>alter table test020t6 add column d int;

--- SQL operation complete.
>>create index test020t6_i1 on test020t6 (d, syskey);

--- SQL operation complete.
>>create index test020t6_i2 on test020t6 (syskey, d);

*** ERROR[1089] The system generated column SYSKEY must be specified last or not specified at all in the index column list.

--- SQL operation failed with errors.
>>
>>-- Expect this to succeed: 
>>create index test020t6_i3 on test020t6 (c, syskey);

--- SQL operation complete.
>>
>>--------------------------------------------
>>-- test cases for solution 10-070515-4764 --
>>--------------------------------------------
>>-- Droppable primary key Store By (no unique) needs SYSKEY column.
>>CREATE TABLE test020t32_S
+>  (
+>    ATEST                                INT NOT NULL
+>  , BTEST                                INT NOT NULL
+>  , CTEST                                INT NOT NULL
+>  , DTEST                                INT NOT NULL
+>  , PRIMARY KEY (ATEST, CTEST)     DROPPABLE
+>  )
+>  STORE BY        (ATEST, CTEST)
+>#ifndef SEABASE_REGRESS
+>  PARTITION BY (CTEST)
+>#endif
+>  ;

--- SQL operation complete.
>>insert into test020t32_S values (11, 12, 13, 14);

--- 1 row(s) inserted.
>>select * from test020t32_S;

ATEST        BTEST        CTEST        DTEST      
-----------  -----------  -----------  -----------

         11           12           13           14

--- 1 row(s) selected.
>>
>>-- This SELECT will show the SYSKEY column
>>
>>-- Droppable primary key Store By Unique doesn't need SYSKEY column.
>>-- Store By Unique (collist) requires CQD DBTR_PROCESS or parserflags.
>>set parserflags 1;

--- SQL operation complete.
>>CREATE TABLE test020t32_NS
+>  (
+>    ATEST                                INT NOT NULL
+>  , BTEST                                INT NOT NULL
+>  , CTEST                                INT NOT NULL
+>  , DTEST                                INT NOT NULL
+>  , PRIMARY KEY (ATEST, CTEST)     DROPPABLE
+>  )
+>  STORE BY UNIQUE (ATEST, CTEST)
+>#ifndef SEABASE_REGRESS
+>  PARTITION BY (CTEST)
+>#endif
+>  ;

--- SQL operation complete.
>>insert into test020t32_NS values (11, 12, 13, 14);

--- 1 row(s) inserted.
>>select * from test020t32_NS;

ATEST        BTEST        CTEST        DTEST      
-----------  -----------  -----------  -----------

         11           12           13           14

--- 1 row(s) selected.
>>
>>-- This SELECT will NOT have the SYSKEY column
>>
>>----------------------------------------
>>-- test cases for case 10-990624-2287 --
>>----------------------------------------
>>
>>create table TEST020T28 (a int not null unique);

--- SQL operation complete.
>>
>>create table TEST020T29 (a int);

--- SQL operation complete.
>>
>>alter table TEST020T29 add constraint TEST020T29CNT1 foreign key (a) references TEST020T28(a);

--- SQL operation complete.
>>
>>insert into TEST020T28 values (1),(2),(3);

--- 3 row(s) inserted.
>>
>>insert into TEST020T29 values (null);

--- 1 row(s) inserted.
>>
>>alter table TEST020T29 drop constraint TEST020T29CNT1;

--- SQL operation complete.
>>
>>insert into TEST020T29 values (1),(null), (3);

--- 3 row(s) inserted.
>>
>>alter table TEST020T29 add constraint TEST020T29CNT2 foreign key (a) references TEST020T28(a);

--- SQL operation complete.
>>
>>select * from TEST020T29;

A          
-----------

          1
          3
          ?
          ?

--- 4 row(s) selected.
>>
>>----------------------------------------
>>-- test cases for case 10-990805-2213 --
>>----------------------------------------
>>create table test020t30 (ssnum char(9) upshift not null unique);

--- SQL operation complete.
>>create table test020t31 (ssnum char(9) upshift not null unique);

--- SQL operation complete.
>>
>>insert into TEST020T30 values ('A23456789');

--- 1 row(s) inserted.
>>insert into TEST020T30 values ('b66666666');

--- 1 row(s) inserted.
>>insert into TEST020T30 values ('C55555555');

--- 1 row(s) inserted.
>>
>>insert into TEST020T31 values ('D22222222');

--- 1 row(s) inserted.
>>insert into TEST020T31 values ('B66666666');

--- 1 row(s) inserted.
>>
>>--this alter should fail
>>alter table TEST020T31 add constraint C44B FOREIGN KEY (ssnum) references TEST020T30(ssnum);

*** ERROR[1143] Validation for constraint TRAFODION.SCH.C44B failed; incompatible data exists in referencing base table TEST020T31 and referenced base table TEST020T30.  To display the data that violates the constraint, please use the following DML statement: select count(*) from "TRAFODION"."SCH"."TEST020T31" where not (("SSNUM") in (select "SSNUM" from "TRAFODION"."SCH"."TEST020T30")) and "SSNUM" is not null ;

--- SQL operation failed with errors.
>>
>>------------------------------------------------------
>>-- Verify that Catalog Manager code issues an error --
>>-- message when RI circular dependency occurs.      --
>>------------------------------------------------------
>>
>>create table test020t17 (c int not null references test020t17 primary key);

--- SQL operation complete.
>>
>>create table test020t18 (c int not null constraint test020t18pk primary key);

--- SQL operation complete.
>>create table test020t19 (c int not null constraint test020t18uq unique
+>                                     constraint test020t19ri
+>                                                references test020t18);

--- SQL operation complete.
>>alter  table test020t18 add constraint test020t18ri foreign key (c)
+>                                              references test020t19 (c);

*** ERROR[1188] Referential integrity constraint TRAFODION.SCH.TEST020T18RI for table TRAFODION.SCH.TEST020T18 could not be created due to circular dependency: .

--- SQL operation failed with errors.
>>
>>create table test020t21 (c1 int not null constraint test020t21uq unique);

--- SQL operation complete.
>>create table test020t22 (c2 int not null constraint test020t22uq unique
+>                                      constraint test020t22ri
+>                                                 references test020t21 (c1));

--- SQL operation complete.
>>create table test020t23 (c3 int not null constraint test020t23uq unique
+>                                      constraint test020t23ri
+>                                                 references test020t22 (c2));

--- SQL operation complete.
>>alter  table test020t21 add constraint test020t21ri foreign key (c1)
+>                                              references test020t23 (c3);

*** ERROR[1188] Referential integrity constraint TRAFODION.SCH.TEST020T21RI for table TRAFODION.SCH.TEST020T21 could not be created due to circular dependency: .

--- SQL operation failed with errors.
>>
>>create table test020t24 (c41 int not null, c42 char(40), c43 int not null,
+>                      constraint test020t24pk primary key (c41, c43));

--- SQL operation complete.
>>create table test020t25 (c51 int not null, c52 int not null,
+>                      constraint test020t25uq unique (c51, c52),
+>                      constraint test020t25ri foreign key (c51, c52)
+>                                           references test020t24);

--- SQL operation complete.
>>create table test020t26 (c61 char(40), c62 int not null, c63 int not null,
+>                      constraint test020t26uq unique (c63, c62),
+>                      constraint test020t26ri foreign key (c63, c62)
+>                                           references test020t25 (c51, c52));

--- SQL operation complete.
>>create table test020t27 (c71 int not null, c72 int not null,
+>                      constraint test020t27pk primary key (c71, c72),
+>                      constraint test020t27ri foreign key (c71, c72)
+>                                           references test020t26 (c63, c62));

--- SQL operation complete.
>>alter  table test020t24 add constraint test020t24ri foreign key (c41, c43)
+>                                              references test020t27;

*** ERROR[1188] Referential integrity constraint TRAFODION.SCH.TEST020T24RI for table TRAFODION.SCH.TEST020T24 could not be created due to circular dependency: .

--- SQL operation failed with errors.
>>
>>-------------------------------------------------------------
>>-- Verify that RI constraint definitions take advantage    --
>>-- of storage keys and existing indexes whenever possible. --
>>-------------------------------------------------------------
>>
>>create table test020t8 (c1 int not null, c2 int not null, c3 int not null,
+> constraint test020t8uq1 unique (c2,c3),
+> constraint test020t8uq2 unique (c1,c2,c3));

--- SQL operation complete.
>>
>>insert into test020t8 values
+>  (1,2,3),    -- to satisfy constraint test020t9ri3
+>  (2,3,1),    -- to satisfy constraint test020t9ri
+>  (11,22,33), -- to satisfy constraint test020t9ri3
+>  (22,33,11);

--- 4 row(s) inserted.
>> -- to satisfy constraint test020t9ri
>>
>>create table test020t9 (r1 int not null, r2 int not null, r3 int not null);

--- SQL operation complete.
>>create unique index test020t9ix on test020t9 (r1,r2);

--- SQL operation complete.
>>showddl test020t9;

CREATE TABLE TRAFODION.SCH.TEST020T9
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T9IX ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r1,r2,r3) of RI constraint test020t9ri
>>-- will take advantage of existing unique index test020t9ix
>>alter table test020t9 add constraint test020t9ri foreign key (r1,r2,r3)
+> references test020t8 (c1,c2,c3);

--- SQL operation complete.
>>showddl test020t9;

CREATE TABLE TRAFODION.SCH.TEST020T9
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T9IX ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI
  FOREIGN KEY
  (
    R1
  , R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

--- SQL operation complete.
>>
>>create table test020t10 (c1 int not null primary key);

--- SQL operation complete.
>>
>>insert into test020t10 values (1), (2), (3), (11), (22);

--- 5 row(s) inserted.
>>
>>-- The foreign key (r1) of RI constraint test020t9ri2
>>-- will take advantage of existing unique index test020t9ix
>>alter table test020t9 add constraint test020t9ri2 foreign key (r1)
+> references test020t10;

--- SQL operation complete.
>>showddl test020t9;

CREATE TABLE TRAFODION.SCH.TEST020T9
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T9IX ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI2 ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI
  FOREIGN KEY
  (
    R1
  , R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI2
  FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r1,r2) of RI constraint test020t9ri3
>>-- will take advantage of existing unique index test020t9ix
>>alter table test020t9 add constraint test020t9ri3 foreign key (r1,r2)
+> references test020t8 (c2,c3);

--- SQL operation complete.
>>showddl test020t9;

CREATE TABLE TRAFODION.SCH.TEST020T9
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T9IX ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI2 ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI
  FOREIGN KEY
  (
    R1
  , R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI2
  FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI3
  FOREIGN KEY
  (
    R1
  , R2
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- insert a row that satisfies RI constraints test020t9ri,
>>-- test020t9ri2 and test020t9ri3
>>insert into test020t9 values (2,3,1);

--- 1 row(s) inserted.
>>
>>-- prepare table t8 for the next RI constraint, test020t9ri4
>>insert into test020t8 values (1,3,2);

--- 1 row(s) inserted.
>>
>>-- The foreign keys (r1,r2) of RI constraint test020t9ri4
>>-- will NOT take advantage of existing unique index test020t9ix.
>>-- A new non-unique index (named test020t9ri4) will be created for
>>-- RI constraint test020t9ri4. Note that we will internally reorder
>>-- the constraint to "foreign key (r2,r1) references ... (c2,c3)"
>>-- to match the unique index on test020t8(c2,c3). This could be
>>-- optimized in the future by re-using the index on test020t9(r1,r2),
>>-- but that would require additional metadata to store the two
>>-- corresponding lists (r1,r2) == (c3,c2) in the KEYS or some other
>>-- table.
>>alter table test020t9 add constraint test020t9ri4 foreign key (r1,r2)
+> references test020t8 (c3,c2);

--- SQL operation complete.
>>showddl test020t9;

CREATE TABLE TRAFODION.SCH.TEST020T9
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T9IX ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI2 ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI4 ON TRAFODION.SCH.TEST020T9
  (
    R2 ASC
  , R1 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI
  FOREIGN KEY
  (
    R1
  , R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI2
  FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI3
  FOREIGN KEY
  (
    R1
  , R2
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI4
  FOREIGN KEY
  (
    R2
  , R1
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- try to insert a row that satisfies  RI constraints test020t9ri,
>>-- test020t9ri2 and test020t9ri3 but not test020t9ri4
>>insert into test020t9 values (22,33,11);

*** ERROR[8103] The operation is prevented by referential integrity constraint TRAFODION.SCH.TEST020T9RI4 on table TRAFODION.SCH.TEST020T9.

--- 0 row(s) inserted.
>>
>>delete from test020t9;

--- 1 row(s) deleted.
>>
>>-- The foreign key (r2) of RI constraint test020t9ri5
>>-- will take advantage of the existing non-unique index test020t9ri4.
>>alter table test020t9 add constraint test020t9ri5 foreign key (r2)
+> references test020t10 (c1);

--- SQL operation complete.
>>showddl test020t9;

CREATE TABLE TRAFODION.SCH.TEST020T9
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T9IX ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI2 ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI4 ON TRAFODION.SCH.TEST020T9
  (
    R2 ASC
  , R1 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI5 ON TRAFODION.SCH.TEST020T9
  (
    R2 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI
  FOREIGN KEY
  (
    R1
  , R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI2
  FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI3
  FOREIGN KEY
  (
    R1
  , R2
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI4
  FOREIGN KEY
  (
    R2
  , R1
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI5
  FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r2,r1,r3) of RI constraint test020t9ri6
>>-- will NOT  take advantage of existing non-unique index test020t9ri4.
>>-- A new non-unique index (named test020t9ri6) will be created for
>>-- RI constraint test020t9ri6.
>>alter table test020t9 add constraint test020t9ri6 foreign key (r2,r1,r3)
+> references test020t8 (c1,c2,c3);

--- SQL operation complete.
>>showddl test020t9;

CREATE TABLE TRAFODION.SCH.TEST020T9
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T9IX ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  , R2 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI2 ON TRAFODION.SCH.TEST020T9
  (
    R1 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI4 ON TRAFODION.SCH.TEST020T9
  (
    R2 ASC
  , R1 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI5 ON TRAFODION.SCH.TEST020T9
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T9RI6 ON TRAFODION.SCH.TEST020T9
  (
    R2 ASC
  , R1 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI
  FOREIGN KEY
  (
    R1
  , R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI2
  FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI3
  FOREIGN KEY
  (
    R1
  , R2
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI4
  FOREIGN KEY
  (
    R2
  , R1
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI5
  FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T9 ADD CONSTRAINT TRAFODION.SCH.TEST020T9RI6
  FOREIGN KEY
  (
    R2
  , R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

--- SQL operation complete.
>>
>>create table test020t11 (r1 int, r2 int not null, r3 int not null,
+> constraint test020t11uq1 unique (r2,r3));

--- SQL operation complete.
>>showddl test020t11;

CREATE TABLE TRAFODION.SCH.TEST020T11
  (
    R1                               INT DEFAULT NULL
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE UNIQUE INDEX TEST020T11UQ1 ON TRAFODION.SCH.TEST020T11
  (
    R2 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11UQ1 UNIQUE
  (
    R2
  , R3
  )
;

--- SQL operation complete.
>>
>>-- The foreign key (r2) of RI constraint test020t11ri1 will NOT
>>-- take advantage of the existing unique index test020t11uq1.
>>-- A new non-unique index (named test020t11ri1) will be created for
>>-- RI constraint test020t11ri1.
>>alter table test020t11 add constraint test020t11ri1
+> foreign key (r3) references test020t10;

--- SQL operation complete.
>>showddl test020t11;

CREATE TABLE TRAFODION.SCH.TEST020T11
  (
    R1                               INT DEFAULT NULL
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T11RI1 ON TRAFODION.SCH.TEST020T11
  (
    R3 ASC
  )
;

-- The following index is a system created index --
CREATE UNIQUE INDEX TEST020T11UQ1 ON TRAFODION.SCH.TEST020T11
  (
    R2 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11UQ1 UNIQUE
  (
    R2
  , R3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11RI1 FOREIGN KEY
  (
    R3
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The foreign key (r3) of RI constraint test020t11ri2 will NOT
>>-- take advantage of the existing unique index test020t11uq1.
>>-- A new non-unique index (named test020t11ri2) will be created
>>-- for RI constraint test020t11ri2.
>>alter table test020t11 add constraint test020t11ri2
+> foreign key (r2) references test020t10;

--- SQL operation complete.
>>showddl test020t11;

CREATE TABLE TRAFODION.SCH.TEST020T11
  (
    R1                               INT DEFAULT NULL
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T11RI1 ON TRAFODION.SCH.TEST020T11
  (
    R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T11RI2 ON TRAFODION.SCH.TEST020T11
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE UNIQUE INDEX TEST020T11UQ1 ON TRAFODION.SCH.TEST020T11
  (
    R2 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11UQ1 UNIQUE
  (
    R2
  , R3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11RI1 FOREIGN KEY
  (
    R3
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11RI2 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r2,r3) of RI constraint test020t11ri3 will
>>-- NOT take advantage of the existing unique index test020t11uq1.
>>-- A new non-unique index (named test020t11ri3) will be created
>>-- for RI constraint test020t11ri3.
>>alter table test020t11 add constraint test020t11ri3
+> foreign key (r2,r3) references test020t8 (c2,c3);

--- SQL operation complete.
>>showddl test020t11;

CREATE TABLE TRAFODION.SCH.TEST020T11
  (
    R1                               INT DEFAULT NULL
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T11RI1 ON TRAFODION.SCH.TEST020T11
  (
    R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T11RI2 ON TRAFODION.SCH.TEST020T11
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T11RI3 ON TRAFODION.SCH.TEST020T11
  (
    R2 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE UNIQUE INDEX TEST020T11UQ1 ON TRAFODION.SCH.TEST020T11
  (
    R2 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11UQ1 UNIQUE
  (
    R2
  , R3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11RI1 FOREIGN KEY
  (
    R3
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11RI2 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T11 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T11RI3 FOREIGN KEY
  (
    R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

--- SQL operation complete.
>>
>>
>>create table test020t12 (r1 int not null, r2 int not null, r3 int not null,
+> r4 int not null) store by (r1, r3);

--- SQL operation complete.
>>showddl test020t12;

CREATE TABLE TRAFODION.SCH.TEST020T12
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>-- The foreign keys (r1,r3) of RI constraint test020t12ri1 will
>>-- take advantage of the existing storage keys (r1,r3).
>>alter table test020t12 add constraint test020t12ri1
+> foreign key (r1,r3) references test020t8 (c2,c3);

--- SQL operation complete.
>>showddl test020t12;

CREATE TABLE TRAFODION.SCH.TEST020T12
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI1 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI1 FOREIGN KEY
  (
    R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- The foreign key (r1) of RI constraint test020t12ri2 will
>>-- take advantage of the existing storage keys (r1,r3).
>>alter table test020t12 add constraint test020t12ri2
+> foreign key (r1) references test020t10;

--- SQL operation complete.
>>showddl test020t12;

CREATE TABLE TRAFODION.SCH.TEST020T12
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI1 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI2 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI1 FOREIGN KEY
  (
    R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI2 FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r1,r3,r4) of RI constraint test020t12ri3
>>-- will NOT take advantage of the existing storage keys (r1,r3).
>>-- A new non-unique index (named test020t12ri3) will be created
>>-- for RI constraint test020t12ri3.
>>alter table test020t12 add constraint test020t12ri3
+> foreign key (r1,r3,r4) references test020t8 (c1,c2,c3);

--- SQL operation complete.
>>showddl test020t12;

CREATE TABLE TRAFODION.SCH.TEST020T12
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI1 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI2 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI3 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  , R3 ASC
  , R4 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI1 FOREIGN KEY
  (
    R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI2 FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI3 FOREIGN KEY
  (
    R1
  , R3
  , R4
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- The foreign key (r3) of RI constraint test020t12ri4 will
>>-- NOT take advantage of the existing storage keys (r1,r3).
>>-- A new non-unique index (named test020t12ri4) will be created
>>-- for RI constraint test020t12ri4.
>>alter table test020t12 add constraint test020t12ri4
+> foreign key (r3) references test020t10;

--- SQL operation complete.
>>showddl test020t12;

CREATE TABLE TRAFODION.SCH.TEST020T12
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI1 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  , R3 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI2 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI3 ON TRAFODION.SCH.TEST020T12
  (
    R1 ASC
  , R3 ASC
  , R4 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T12RI4 ON TRAFODION.SCH.TEST020T12
  (
    R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI1 FOREIGN KEY
  (
    R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI2 FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI3 FOREIGN KEY
  (
    R1
  , R3
  , R4
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T12 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T12RI4 FOREIGN KEY
  (
    R3
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>create table test020t13 (r1 int not null, r2 int not null, r3 int not null,
+> r4 int, constraint test020t13pk primary key (r2,r3));

--- SQL operation complete.
>>showddl test020t13;

CREATE TABLE TRAFODION.SCH.TEST020T13
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT DEFAULT NULL
  , PRIMARY KEY (R2 ASC, R3 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>-- The foreign keys (r2,r3) of RI constraint test020t13ri1 will
>>-- take advantage of the existing storage keys (the columns of
>>-- the non-droppable primary key constraint test020t13pk).
>>alter table test020t13 add constraint test020t13ri1
+> foreign key (r2,r3) references test020t8 (c2,c3);

--- SQL operation complete.
>>showddl test020t13;

CREATE TABLE TRAFODION.SCH.TEST020T13
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT DEFAULT NULL
  , PRIMARY KEY (R2 ASC, R3 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI1 FOREIGN KEY
  (
    R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- The foreign key (r2) of RI constraint test020t13ri2 will
>>-- take advantage of the existing storage keys (the columns of
>>-- the non-droppable primary key constraint test020t13pk).
>>alter table test020t13 add constraint test020t13ri2
+> foreign key (r2) references test020t10;

--- SQL operation complete.
>>showddl test020t13;

CREATE TABLE TRAFODION.SCH.TEST020T13
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT DEFAULT NULL
  , PRIMARY KEY (R2 ASC, R3 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T13RI2 ON TRAFODION.SCH.TEST020T13
  (
    R2 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI1 FOREIGN KEY
  (
    R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI2 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r2,r3,r4) of RI constraint test020t13ri3 will
>>-- take advantage of the existing storage keys (the columns of
>>-- the non-droppable primary key constraint test020t13pk).
>>alter table test020t13 add constraint test020t13ri3
+> foreign key (r2,r3,r4) references test020t8 (c1,c2,c3);

--- SQL operation complete.
>>showddl test020t13;

CREATE TABLE TRAFODION.SCH.TEST020T13
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT DEFAULT NULL
  , PRIMARY KEY (R2 ASC, R3 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T13RI2 ON TRAFODION.SCH.TEST020T13
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T13RI3 ON TRAFODION.SCH.TEST020T13
  (
    R2 ASC
  , R3 ASC
  , R4 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI1 FOREIGN KEY
  (
    R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI2 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI3 FOREIGN KEY
  (
    R2
  , R3
  , R4
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r1,r2,r3) of RI constraint test020t11ri4
>>-- will NOT take advantage of the storage keys (r2,r3).  A
>>-- new non-unique index (named test020t13ri4) will be created
>>-- for RI constraint test020t13ri4.
>>alter table test020t13 add constraint test020t13ri4
+> foreign key (r1,r2,r3) references test020t8 (c1,c2,c3);

--- SQL operation complete.
>>showddl test020t13;

CREATE TABLE TRAFODION.SCH.TEST020T13
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R4                               INT DEFAULT NULL
  , PRIMARY KEY (R2 ASC, R3 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE INDEX TEST020T13RI2 ON TRAFODION.SCH.TEST020T13
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T13RI3 ON TRAFODION.SCH.TEST020T13
  (
    R2 ASC
  , R3 ASC
  , R4 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T13RI4 ON TRAFODION.SCH.TEST020T13
  (
    R1 ASC
  , R2 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI1 FOREIGN KEY
  (
    R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI2 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI3 FOREIGN KEY
  (
    R2
  , R3
  , R4
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T13 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T13RI4 FOREIGN KEY
  (
    R1
  , R2
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C1
  , C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- Note that the primary key (r2) of constraint test020t14pk is
>>-- not the storage key.  A unique index (named test020t14pk) will be
>>-- created for primary key constraint test020t14pk.
>>create table test020t14 (r1 int not null, r2 int not null, r3 int not null,
+>-- constraint test020t14pk primary key (r2) droppable)
+>  constraint test020t14pk unique(r2) )
+> store by (r1,r3,r2);

--- SQL operation complete.
>>showddl test020t14;

CREATE TABLE TRAFODION.SCH.TEST020T14
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC, R2 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE UNIQUE INDEX TEST020T14PK ON TRAFODION.SCH.TEST020T14
  (
    R2 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT TRAFODION.SCH.TEST020T14PK
  UNIQUE
  (
    R2
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r1,r3) of RI constraint test020t14ri1 will
>>-- take advantage of the existing storage keys (r1,r3,r2).
>>alter table test020t14 add constraint test020t14ri1
+> foreign key (r1,r3) references test020t8 (c2,c3);

--- SQL operation complete.
>>showddl test020t14;

CREATE TABLE TRAFODION.SCH.TEST020T14
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC, R2 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

-- The following index is a system created index --
CREATE UNIQUE INDEX TEST020T14PK ON TRAFODION.SCH.TEST020T14
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T14RI1 ON TRAFODION.SCH.TEST020T14
  (
    R1 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT TRAFODION.SCH.TEST020T14PK
  UNIQUE
  (
    R2
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T14RI1 FOREIGN KEY
  (
    R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

--- SQL operation complete.
>>
>>create index test020t14ndx on test020t14(r2);

--- SQL operation complete.
>>showddl test020t14;

CREATE TABLE TRAFODION.SCH.TEST020T14
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC, R2 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

CREATE INDEX TEST020T14NDX ON TRAFODION.SCH.TEST020T14
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE UNIQUE INDEX TEST020T14PK ON TRAFODION.SCH.TEST020T14
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T14RI1 ON TRAFODION.SCH.TEST020T14
  (
    R1 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT TRAFODION.SCH.TEST020T14PK
  UNIQUE
  (
    R2
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T14RI1 FOREIGN KEY
  (
    R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- The foreign key (r1) of RI constraint test020t14ri2 will
>>-- take advantage of the existing index test020t14ndx.
>>alter table test020t14 add constraint test020t14ri2
+> foreign key (r2) references test020t10;

--- SQL operation complete.
>>showddl test020t14;

CREATE TABLE TRAFODION.SCH.TEST020T14
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC, R2 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

CREATE INDEX TEST020T14NDX ON TRAFODION.SCH.TEST020T14
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE UNIQUE INDEX TEST020T14PK ON TRAFODION.SCH.TEST020T14
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T14RI1 ON TRAFODION.SCH.TEST020T14
  (
    R1 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT TRAFODION.SCH.TEST020T14PK
  UNIQUE
  (
    R2
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T14RI1 FOREIGN KEY
  (
    R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T14RI2 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- This will remove the system defined index created when the primary key
>>-- was created
>>alter table test020t14 drop constraint test020t14pk;

--- SQL operation complete.
>>showddl test020t14;

CREATE TABLE TRAFODION.SCH.TEST020T14
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  STORE BY (R1 ASC, R3 ASC, R2 ASC)
 ATTRIBUTES ALIGNED FORMAT
;

CREATE INDEX TEST020T14NDX ON TRAFODION.SCH.TEST020T14
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T14RI1 ON TRAFODION.SCH.TEST020T14
  (
    R1 ASC
  , R3 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T14RI1 FOREIGN KEY
  (
    R1
  , R3
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T14 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T14RI2 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>create table test020t15 (r1 int not null,
+> constraint test020t15pk primary key (r1) droppable);

--- SQL operation complete.
>>showddl test020t15;

CREATE TABLE TRAFODION.SCH.TEST020T15
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (R1 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>-- The foreign key (r1) of RI constraint test020t15ri1
>>-- will NOT take advantage of the unique index test020t15pk
>>-- because it associates with the droppable primary key
>>-- constraint test020t15pk.  A new non-unique index (named
>>-- test020t15ri1) will be created for RI constraint test020t15ri1.
>>alter table test020t15 add constraint test020t15ri1
+> foreign key (r1) references test020t10;

--- SQL operation complete.
>>showddl test020t15;

CREATE TABLE TRAFODION.SCH.TEST020T15
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (R1 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

ALTER TABLE TRAFODION.SCH.TEST020T15 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T15RI1 FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>create table test020t16 (r1 int not null, r2 int not null, r3 int not null);

--- SQL operation complete.
>>create unique index test020t16ix1 on test020t16 (r2);

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

--- SQL operation complete.
>>
>>-- The user should be able to drop the index test020t16ix1.
>>drop index test020t16ix1;

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>-- Define the index test02016ix1 again.
>>create unique index test020t16ix1 on test020t16 (r2);

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

--- SQL operation complete.
>>
>>-- The foreign key (r2) of RI constraint test020t16ri1 will
>>-- take advantage of the existing unique index test020t16ix1.
>>alter table test020t16 add constraint test020t16ri1
+> foreign key (r2) references test020t10;

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The user can no longer remove the index test020t16ix1 because it
>>-- is used by the foreign key (r2) of RI constraint test020t16ri1.
>>drop index test020t16ix1;

*** ERROR[1059] Request failed.  Dependent constraint  exists.

--- SQL operation failed with errors.
>>
>>create unique index test020t16ix2 on test020t16 (r1,r2);

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

CREATE UNIQUE INDEX TEST020T16IX2 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  , R2 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The foreign keys (r1,r2) of RI constraint test020t16ri2 will
>>-- take advantage of the existing unique index test020t16ix2.
>>alter table test020t16 add constraint test020t16ri2
+> foreign key (r1,r2) references test020t8 (c2,c3);

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

CREATE UNIQUE INDEX TEST020T16IX2 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  , R2 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI2 FOREIGN KEY
  (
    R1
  , R2
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

--- SQL operation complete.
>>
>>-- The user can no longer remove the index test020t16ix1 because it
>>-- is used by the foreign key (r1,r2) of RI constraint test020t16ri2.
>>drop index test020t16ix2;

*** ERROR[1059] Request failed.  Dependent constraint  exists.

--- SQL operation failed with errors.
>>
>>-- The foreign key (r1) of RI constraint test020t16ri3 will
>>-- take advantage of the existing unique index test020t16ix2.
>>alter table test020t16 add constraint test020t16ri3
+> foreign key (r1) references test020t10;

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

CREATE UNIQUE INDEX TEST020T16IX2 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T16RI3 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI2 FOREIGN KEY
  (
    R1
  , R2
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI3 FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The user cannot remove the index test020t16ix1 because it is
>>-- used by the foreign key (r1) of RI constraint test020t16ri3
>>-- and the foreign keys (r1,r2) of RI constraint test020t16ri2.
>>drop index test020t16ix2;

*** ERROR[1059] Request failed.  Dependent constraint  exists.

--- SQL operation failed with errors.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

CREATE UNIQUE INDEX TEST020T16IX2 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T16RI3 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI2 FOREIGN KEY
  (
    R1
  , R2
  )
 REFERENCES TRAFODION.SCH.TEST020T8
  (
    C2
  , C3
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI3 FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>alter table test020t16 drop constraint test020t16ri2;

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

CREATE UNIQUE INDEX TEST020T16IX2 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  , R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T16RI3 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI3 FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The user is still not able to remove the index test020t16ix1 because
>>-- it is used by the foreign key (r1) of RI constraint test020t16ri3.
>>drop index test020t16ix2;

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

-- The following index is a system created index --
CREATE INDEX TEST020T16RI3 ON TRAFODION.SCH.TEST020T16
  (
    R1 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI3 FOREIGN KEY
  (
    R1
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>alter table test020t16 drop constraint test020t16ri3;

--- SQL operation complete.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- The user should be able to remove index test020t16ix2 now because
>>-- the index no longer assocates with any constraints.
>>drop index test020t16ix2;

*** ERROR[1389] Object TRAFODION.SCH.TEST020T16IX2 does not exist in Trafodion.

--- SQL operation failed with errors.
>>showddl test020t16;

CREATE TABLE TRAFODION.SCH.TEST020T16
  (
    R1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , R3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  )
 ATTRIBUTES ALIGNED FORMAT
;

CREATE UNIQUE INDEX TEST020T16IX1 ON TRAFODION.SCH.TEST020T16
  (
    R2 ASC
  )
;

ALTER TABLE TRAFODION.SCH.TEST020T16 ADD CONSTRAINT
  TRAFODION.SCH.TEST020T16RI1 FOREIGN KEY
  (
    R2
  )
 REFERENCES TRAFODION.SCH.TEST020T10
  (
    C1
  )
;

--- SQL operation complete.
>>
>>-- Table with key columns in not-natural order.
>>-- SHOWDDL should get it right, both in this process and in another.
>>create table test020t20 (c1 int not null, c2 int not null, c3 int not null, 
+>  primary key (c3, c1));

--- SQL operation complete.
>>showddl test020t20;

CREATE TABLE TRAFODION.SCH.TEST020T20
  (
    C1                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C2                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , C3                               INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (C3 ASC, C1 ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

--- SQL operation complete.
>>
>>obey TEST020(test_10_020913_3920);
>>-- Set up test case using female_actors, male_actors, directors, movie_titles
>>create table test020_female_actors 
+>  (f_no int not null,
+>   f_name varchar(30) not null,
+>   f_realname varchar(50) default null,
+>   f_birthday date constraint TEST020_mdl check (f_birthday > date '1900-02-02'),
+>   primary key (f_no) not droppable);

--- SQL operation complete.
>>
>>create table test020_male_actors
+>  (m_no int not null not droppable unique,
+>   m_name varchar (30) not null,
+>   m_realname varchar (50) default null,
+>   m_birthday date constraint TEST020_md2 check (m_birthday > date '1900-01-01'));

--- SQL operation complete.
>>
>>create table test020_directors 
+>  (d_no int not null not droppable,
+>   d_name varchar (20) not null,
+>  "d_specialty" varchar (15) not null,
+>   primary key (d_no),
+>   constraint TEST020_td1 check ("d_specialty" <> 'Music Video'),
+>   unique (d_no, "d_specialty"));

--- SQL operation complete.
>>
>>insert into test020_directors values
+>  (0, 'no director named', 'unknown'),
+>  (1234, 'Alfred Hitchcock', 'Mystery'),
+>  (1345, 'Clint Eastwood', 'Action'),
+>  (1456, 'Fred Zinneman', 'Western'),
+>  (1567, 'George Cukor', 'Drama'),
+>  (1789, 'Roger Corman', 'Scary');

--- 6 row(s) inserted.
>>
>>insert into test020_male_actors values 
+>  (0, 'no male actor', 'no male actor', current_date),
+>  (1111, 'Cary Grant', 'Archibold Alic Leach', date '1904-01-18'),
+>  (1222, 'Gary Cooper', 'Frank James Cooper', date '1901-05-07'),
+>  (1333, 'Clint Eastwood', 'Clinton Eastwood Jr', date '1930-05-31');

--- 4 row(s) inserted.
>>
>>insert into test020_female_actors values
+>  (0, 'no female actor', 'no female actor', current_date),
+>  (6111, 'Grace Kelly', 'Grace Patricia Kelly', date '1929-11-12'),
+>  (6123, 'Katherine Hepburn', 'Katherin Houghlin Hepburn', date '1907-05-12'),
+>  (6124, 'Joan Crawford', 'Lucille Fay LeSueyr', date '1904-03-23'),
+>  (6125, 'Ingrid Bergman', 'Ingrid Bergman', date '1915-08-29');

--- 5 row(s) inserted.
>>
>>create table test020_movie_titles
+>  (mv_no int not null,
+>   mv_name varchar (40) not null,
+>   mv_malestar int default NULL constraint test020_ma_fk 
+>      references test020_male_actors(m_no),
+>   mv_femalestar int default NULL,
+>   mv_director int default 0 not null,
+>   mv_yearmade int check (mv_yearmade > 1901),
+>   mv_star_rating char (4),
+>   mv_movietype varchar (15),
+>   primary key (mv_no) not droppable,
+>   constraint TEST020_d_fk foreign key (mv_director, mv_movietype) references
+>    test020_directors (d_no, "d_specialty"));

--- SQL operation complete.
>>
>>insert into test020_movie_titles values
+>  (1, 'To Catch a Thief', 1111,6111,1234,1955, '****', 'Mystery'),
+>  (2, 'High Noon', 1222,6111,1456,1951, '****', 'Western'),
+>  (3, 'Unforgiven', 1333,0,1345,1990, '***', 'Action'),
+>  (4, 'The Women', 0, 6124, 1567, 1939, '****', 'Drama'),
+>  (5, 'The Philadelphia Story', 1111,6123,1567, 1940, '****', 'Drama'),
+>  (6, 'Notorious', 1111, 6125, 1234,1946, '****', 'Mystery');

--- 6 row(s) inserted.
>>
>>-- This test was cause error 1082 to return in Genesis case 10-020913-3920
>>-- After the fix, this test should succeed.
>>alter table test020_movie_titles 
+>   add constraint test020_fa_fk 
+>       foreign key (mv_femalestar) references test020_female_actors;

--- SQL operation complete.
>>
>>obey TEST020(test_10_030916_9668);
>>create table test020_10_030916_9668 (col1 interval minute(2) to second(2) not null,
+>  primary key (col1)) ;

--- SQL operation complete.
>>
>>insert into test020_10_030916_9668 values (interval '96:59.8' minute to second);

--- 1 row(s) inserted.
>>select * from test020_10_030916_9668;

COL1     
---------

 96:59.80

--- 1 row(s) selected.
>>
>>obey TEST020(test_LP_1360493);
>>create table tmp_LP_1360493
+> (sbin0_4 integer not null
+> , varchar0_500 varchar(11) default 'GDAAIAAA' not null heading 'varchar0_500 no nulls'
+> , sdec16_uniq numeric(18,0) signed not null
+> );

--- SQL operation complete.
>>
>>create view view_LP_1360493
+> as select * from tmp_LP_1360493 where sdec16_uniq > 3000
+> union
+> select * from tmp_LP_1360493 where sdec16_uniq < 2500;

--- SQL operation complete.
>>
>>create table test020_LP_1360493
+> ( vch15 varchar(15)
+> , nint integer
+> , ch3 char(3)
+> , nlarge largeint);

--- SQL operation complete.
>>
>>insert into test020_LP_1360493
+> values ('1st orig value' ,99 , 'o' , 1 )
+> , ('2nd orig value' ,98 , 'ov' , 2 )
+> , ('5th orig value' ,95 , 'o ' , 5 )
+> , ('6 is short' ,97 , 'o' , 6 )
+> , ('7' ,94 , 'OVC' , 7 );

--- 5 row(s) inserted.
>>
>>select * from test020_LP_1360493;

VCH15            NINT         CH3  NLARGE              
---------------  -----------  ---  --------------------

1st orig value            99  o                       1
2nd orig value            98  ov                      2
5th orig value            95  o                       5
6 is short                97  o                       6
7                         94  OVC                     7

--- 5 row(s) selected.
>>
>>update test020_LP_1360493 set vch15 = (select max(c)
+>from (select varchar0_500 from view_LP_1360493 ) dt(c)) where nint=95;

--- 1 row(s) updated.
>>
>>drop table tmp_LP_1360493 cascade;

--- SQL operation complete.
>>
>>select * from test020_LP_1360493;

VCH15            NINT         CH3  NLARGE              
---------------  -----------  ---  --------------------

1st orig value            99  o                       1
2nd orig value            98  ov                      2
?                         95  o                       5
6 is short                97  o                       6
7                         94  OVC                     7

--- 5 row(s) selected.
>>
>>-- enforced option
>>create table test020_t33 (a int not null primary key);

--- SQL operation complete.
>>create table test020_t34 (a int not null primary key, b int not null,
+>   constraint t34_1 foreign key (b) references test020_t33 not enforced);

*** WARNING[1313] The referential integrity constraint TRAFODION.SCH.T34_1 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>showddl test020_t34;

CREATE TABLE TRAFODION.SCH.TEST020_T34
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

ALTER TABLE TRAFODION.SCH.TEST020_T34 ADD CONSTRAINT TRAFODION.SCH.T34_1
  FOREIGN KEY
  (
    B
  )
 REFERENCES TRAFODION.SCH.TEST020_T33
  (
    A
  )
 NOT ENFORCED
;

--- SQL operation complete.
>>-- should succeed, ref constr not enforced
>>insert into test020_t34 values (1,1);

--- 1 row(s) inserted.
>>
>>delete from test020_t34;

--- 1 row(s) deleted.
>>alter table test020_t34 drop constraint t34_1;

--- SQL operation complete.
>>alter table test020_t34 add constraint t34_1 foreign key(b) references test020_t33(a) not enforced;

*** WARNING[1313] The referential integrity constraint TRAFODION.SCH.T34_1 has been created with the NOT ENFORCED attribute and will not be enforced during INSERT, UPDATE, OR DELETE statements.

--- SQL operation complete.
>>showddl test020_t34;

CREATE TABLE TRAFODION.SCH.TEST020_T34
  (
    A                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , B                                INT NO DEFAULT NOT NULL NOT DROPPABLE
  , PRIMARY KEY (A ASC)
  )
 ATTRIBUTES ALIGNED FORMAT
;

ALTER TABLE TRAFODION.SCH.TEST020_T34 ADD CONSTRAINT TRAFODION.SCH.T34_1
  FOREIGN KEY
  (
    B
  )
 REFERENCES TRAFODION.SCH.TEST020_T33
  (
    A
  )
 NOT ENFORCED
;

--- SQL operation complete.
>>-- should succeed, ref constr not enforced
>>insert into test020_t34 values (1,1);

--- 1 row(s) inserted.
>>
>>-- self referencing constraints
>>cqd traf_allow_self_ref_constr 'ON';

--- SQL operation complete.
>>create table test020t40 (a int not null primary key, b int not null);

--- SQL operation complete.
>>alter table test020t40 add constraint test020tu1 unique (b);

--- SQL operation complete.
>>alter table test020t40 add constraint test020tc1 foreign key (b) references test020t40(a);

--- SQL operation complete.
>>alter table test020t40 add constraint test020tc2 foreign key (a) references test020t40(b);

--- SQL operation complete.
>>
>>-- next 2 drops should fail
>>alter table test020_t34 drop constraint test020tu1;

*** ERROR[1052] Constraint cannot be dropped because it does not belong to the specified table.

--- SQL operation failed with errors.
>>alter table test020_t34 drop constraint test020tc1;

*** ERROR[1052] Constraint cannot be dropped because it does not belong to the specified table.

--- SQL operation failed with errors.
>>
>>alter table test020t40 drop constraint test020tc2;

--- SQL operation complete.
>>alter table test020t40 drop constraint test020tc1;

--- SQL operation complete.
>>drop table test020t40;

--- SQL operation complete.
>>
>>-- test for drop column
>>set parserflags 1;

--- SQL operation complete.
>>create table test020t40 (a int not null primary key, b int not null, c int not null);

--- SQL operation complete.
>>create index test020t40i1 on test020t40(c);

--- SQL operation complete.
>>insert into test020t40 values (1,1,1), (2,2,2);

--- 2 row(s) inserted.
>>select * from test020t40;

A            B            C          
-----------  -----------  -----------

          1            1            1
          2            2            2

--- 2 row(s) selected.
>>select * from table(index_table test020t40i1);

C@           A          
-----------  -----------

          1            1
          2            2

--- 2 row(s) selected.
>>alter table test020t40 drop column b;

--- SQL operation complete.
>>select * from test020t40;

A            C          
-----------  -----------

          1            1
          2            2

--- 2 row(s) selected.
>>insert into test020t40 values (3,3,3);

*** ERROR[4023] The degree of each row value constructor (3) must equal the degree of the target table column list (2).

*** ERROR[8822] The statement was not prepared.

>>insert into test020t40 values (3,3);

--- 1 row(s) inserted.
>>select * from test020t40;

A            C          
-----------  -----------

          1            1
          2            2
          3            3

--- 3 row(s) selected.
>>select * from table(index_table test020t40i1);

C@           A          
-----------  -----------

          1            1
          2            2
          3            3

--- 3 row(s) selected.
>>
>>obey TEST020(trafodion_1700_and_1847);
>>set parserflags 1;

--- SQL operation complete.
>>--test for timestamp column default value
>>cqd traf_upsert_mode 'merge';

--- SQL operation complete.
>>cqd traf_aligned_row_format 'off' ;

--- SQL operation complete.
>>create table test020t41(a largeint not null primary key, b char(10),
+>c timestamp(6) default current , d int , e int default 3);

--- SQL operation complete.
>>-- check if the timestamp is inserted with the recent timestamp
>>insert into test020t41 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>select a,b,d,e from test020t41 where current_timestamp-c < cast(10 as interval second);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  a                     ?            3
                   2  b                     ?            3

--- 2 row(s) selected.
>>-- check to ensure the timestamp column is not updated with upsert
>>upsert into test020t41 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>select a,b,d,e from test020t41 where a = 1 and c = (select c from test020t41 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>-- check to ensure the value for column e is retained
>>upsert into test020t41 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>select a,b,d,e from test020t41 where a = 1 and c = (select c from test020t41 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            5

--- 1 row(s) selected.
>>-- upsert with non-matching rows  
>>upsert into test020t41 (a,b) values (3, 'e'), (4, 'f');

--- 2 row(s) inserted.
>>select a,b,d,e from test020t41 ;

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            5
                   2  b                     ?            3
                   3  e                     ?            3
                   4  f                     ?            3

--- 4 row(s) selected.
>>upsert into test020t41 (a,b) values (3, 'g');

--- 1 row(s) inserted.
>>select a,b,d,e from test020t41 where a = 3 and c = (select c from test020t41 where a = 4);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   3  g                     ?            3

--- 1 row(s) selected.
>>create index test020t41ix on test020t41(e);

--- SQL operation complete.
>>select * from table(index_table test020t41ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          5                     1

--- 4 row(s) selected.
>>upsert into test020t41 (a,b,e) values (5,'h',6);

--- 1 row(s) inserted.
>>select * from table(index_table test020t41ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          5                     1
          6                     5

--- 5 row(s) selected.
>>-- check if the updated e column is reflected in the index
>>upsert into test020t41 (a,b,e) values (1, 'c', 4);

--- 1 row(s) inserted.
>>select * from table(index_table test020t41ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          4                     1
          6                     5

--- 5 row(s) selected.
>>
>>--  With index it will be merge anyway, but default values needs to be 
>>--  populated
>>cqd traf_upsert_mode 'replace';

--- SQL operation complete.
>>delete from test020t41 ;

--- 5 row(s) deleted.
>>insert into test020t41 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>upsert into test020t41 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>-- Should display a row with = 1
>>select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>upsert into test020t41 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>-- Should display a row with = 1 and e should be 3
>>select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            3

--- 1 row(s) selected.
>>select * from table(index_table test020t41ix) ;

E@           A                   
-----------  --------------------

          3                     1
          3                     2

--- 2 row(s) selected.
>>drop index test020t41ix ;

--- SQL operation complete.
>>-- Without index 
>>delete from test020t41 ;

--- 2 row(s) deleted.
>>insert into test020t41 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>upsert into test020t41 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>-- Should display a row with = 1
>>select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>upsert into test020t41 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>-- Should display a row with = 1 and e should be 3
>>select a,b,d,e from test020t41 where c > (select c from test020t41 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            3

--- 1 row(s) selected.
>>
>>cqd traf_upsert_mode 'optimal' ;

--- SQL operation complete.
>>-- check if the timestamp is inserted with the recent timestamp
>>delete from test020t41 ;

--- 2 row(s) deleted.
>>insert into test020t41 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>select a,b,d,e from test020t41 where current_timestamp-c < cast(10 as interval second);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  a                     ?            3
                   2  b                     ?            3

--- 2 row(s) selected.
>>-- check to ensure the timestamp column is not updated with upsert
>>upsert into test020t41 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>select a,b,d,e from test020t41 where a = 1 and c = (select c from test020t41 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>-- check to ensure the value for column e is retained
>>upsert into test020t41 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>select a,b,d,e from test020t41 where a = 1 and c = (select c from test020t41 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            5

--- 1 row(s) selected.
>>-- upsert with non-matching rows  
>>upsert into test020t41 (a,b) values (3, 'e'), (4, 'f');

--- 2 row(s) inserted.
>>select a,b,d,e from test020t41 ;

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            5
                   2  b                     ?            3
                   3  e                     ?            3
                   4  f                     ?            3

--- 4 row(s) selected.
>>upsert into test020t41 (a,b) values (3, 'g');

--- 1 row(s) inserted.
>>select a,b,d,e from test020t41 where a = 3 and c = (select c from test020t41 where a = 4);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   3  g                     ?            3

--- 1 row(s) selected.
>>create index test020t41ix on test020t41(e);

--- SQL operation complete.
>>select * from table(index_table test020t41ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          5                     1

--- 4 row(s) selected.
>>upsert into test020t41 (a,b,e) values (5,'h',6);

--- 1 row(s) inserted.
>>select * from table(index_table test020t41ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          5                     1
          6                     5

--- 5 row(s) selected.
>>-- check if the updated e column is reflected in the index
>>upsert into test020t41 (a,b,e) values (1, 'c', 4);

--- 1 row(s) inserted.
>>select * from table(index_table test020t41ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          4                     1
          6                     5

--- 5 row(s) selected.
>>
>>create table test020t42(a largeint not null primary key, b char(10),
+>c timestamp(6) default current , d int , e int default 3) attribute aligned format;

--- SQL operation complete.
>>cqd traf_upsert_mode 'merge';

--- SQL operation complete.
>>-- check if the timestamp is inserted with the recent timestamp
>>insert into test020t42 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>select a,b,d,e from test020t42 where current_timestamp-c < cast(10 as interval second);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  a                     ?            3
                   2  b                     ?            3

--- 2 row(s) selected.
>>-- check to ensure the timestamp column is not updated with upsert
>>upsert into test020t42 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>select a,b,d,e from test020t42 where a = 1 and c = (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>-- check to ensure the value for column d is retained
>>upsert into test020t42 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>select a,b,d,e from test020t42 where a = 1 and c = (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            5

--- 1 row(s) selected.
>>-- upsert with non-matching rows  
>>upsert into test020t42 (a,b) values (3, 'e'), (4, 'f');

--- 2 row(s) inserted.
>>select a,b,d,e from test020t42 ;

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            5
                   2  b                     ?            3
                   3  e                     ?            3
                   4  f                     ?            3

--- 4 row(s) selected.
>>upsert into test020t42 (a,b) values (3, 'g');

--- 1 row(s) inserted.
>>select a,b,d,e from test020t42 where a = 3 and c = (select c from test020t42 where a = 4);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   3  g                     ?            3

--- 1 row(s) selected.
>>create index test020t42ix on test020t42(e);

--- SQL operation complete.
>>select * from table(index_table test020t42ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          5                     1

--- 4 row(s) selected.
>>upsert into test020t42 (a,b,e) values (5,'h',6);

--- 1 row(s) inserted.
>>select * from table(index_table test020t42ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          5                     1
          6                     5

--- 5 row(s) selected.
>>-- check if the updated d column is reflected in the index
>>upsert into test020t42 (a,b,e) values (1, 'c', 4);

--- 1 row(s) inserted.
>>select * from table(index_table test020t42ix) ;

E@           A                   
-----------  --------------------

          3                     2
          3                     3
          3                     4
          4                     1
          6                     5

--- 5 row(s) selected.
>>
>>--  With index it will be merge anyway, but default values needs to be 
>>--  populated
>>cqd traf_upsert_mode 'replace';

--- SQL operation complete.
>>delete from test020t42 ;

--- 5 row(s) deleted.
>>insert into test020t42 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>upsert into test020t42 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>-- Should display a row with = 1
>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>upsert into test020t42 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>-- Should display a row with = 1 and e should be 3
>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            3

--- 1 row(s) selected.
>>select * from table(index_table test020t42ix) ;

E@           A                   
-----------  --------------------

          3                     1
          3                     2

--- 2 row(s) selected.
>>drop index test020t42ix ;

--- SQL operation complete.
>>-- Without index 
>>delete from test020t42 ;

--- 2 row(s) deleted.
>>insert into test020t42 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>upsert into test020t42 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>-- Should display a row with = 1
>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>upsert into test020t42 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>-- Should display a row with = 1 and e should be 3
>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            3

--- 1 row(s) selected.
>>
>>cqd traf_upsert_mode 'optimal';

--- SQL operation complete.
>>delete from test020t42 ;

--- 2 row(s) deleted.
>>create index test020t42ix on test020t42(e);

--- SQL operation complete.
>>insert into test020t42 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>upsert into test020t42 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>-- Should display a row with = 1
>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>upsert into test020t42 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>-- Should display a row with = 1 and e should be 3
>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            3

--- 1 row(s) selected.
>>select * from table(index_table test020t42ix) ;

E@           A                   
-----------  --------------------

          3                     1
          3                     2

--- 2 row(s) selected.
>>drop index test020t42ix ;

--- SQL operation complete.
>>-- Without index 
>>delete from test020t42 ;

--- 2 row(s) deleted.
>>insert into test020t42 (a,b) values (1,'a'), (2,'b');

--- 2 row(s) inserted.
>>upsert into test020t42 (a,b,e) values (1, 'c', 5);

--- 1 row(s) inserted.
>>-- Should display a row with = 1
>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  c                     ?            5

--- 1 row(s) selected.
>>upsert into test020t42 (a,b) values (1, 'd');

--- 1 row(s) inserted.
>>-- Should display a row with = 1 and e should be 3
>>select a,b,d,e from test020t42 where c > (select c from test020t42 where a = 2);

A                     B           D            E          
--------------------  ----------  -----------  -----------

                   1  d                     ?            3

--- 1 row(s) selected.
>>
>>create table test020t43(c1 int, c2 int ) attribute aligned format ;

--- SQL operation complete.
>>cqd traf_upsert_mode 'merge';

--- SQL operation complete.
>>upsert into test020t43 values (1,1);

--- 1 row(s) inserted.
>>upsert into test020t43 (c1) values(1);

--- 1 row(s) inserted.
>>select * from test020t43 ;

C1           C2         
-----------  -----------

          1            1
          1            ?

--- 2 row(s) selected.
>>
>>obey TEST020(trafodion_2247);
>>create table test020t44(a char(15) not null primary key,b int) 
+>attribute aligned format;

--- SQL operation complete.
>>alter table test020t44 add c int;

--- SQL operation complete.
>>cqd traf_upsert_mode 'merge';

--- SQL operation complete.
>>upsert into test020t44 (a,c) values ('AAAA', 2);

--- 1 row(s) inserted.
>>select * from test020t44;

A                B            C          
---------------  -----------  -----------

AAAA                       ?            2

--- 1 row(s) selected.
>>
>>log;
