Archives of the TeradataForum

Message Posted: Thu, 11 May 2006 @ 14:52:27 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Note that SET tables don't "reject" duplicate rows, they just don't store them. That subtle distinction means there is a difference between SET and MULTISET behavior even when you have a UNIQUE index:

      /* Create two one-row tables, identical except for SET or MULTISET */
     create set table upi_set (TheKey integer, TheRest char(10))
      unique primary index(TheKey);
     create multiset table upi_multiset (TheKey integer, TheRest char(10))
      unique primary index (TheKey);
     insert into upi_set values (1,'Same');
     insert into upi_multiset values (1,'Same');

      /* For MULTISET table, duplicate key always causes an error */

     insert into upi_multiset select * from upi_set;
      *** Failure 2801 Duplicate unique prime key error in yyy.upi_multiset.

      /* But for SET table entirely duplicate ROWS are ignored and do not
     cause an error */

     insert into upi_set select * from upi_multiset;
      *** Insert completed. No rows added.

      /* If index values are duplicate but the rest of the row isn't,
         you do get an error even with SET table */

     update upi_set set TheRest='Different' where TheKey=1;
      *** Update completed. One row changed.

     insert into upi_set select * from upi_multiset;
      *** Failure 2801 Duplicate unique prime key error in yyy.upi_set.

	<Prev	Next>		<<First	<Prev	Next>	Last>>