vsbabu.org

Preventing duplicates in VARRAY columns

Question: “A table has a VARRAY column to store phone numbers. How can you create a trigger to ensure that no duplicate values are stored in that column?”

Generally, I tend not to use Oracle’s extensions - for portability. But this was an interesting question.

Here is the SQL*Plus transcript that illustrates a sample solution. It assumes Oracle’s sample schema (scott/tiger) is installed.

scott@vsb>REM assumes sample scott/tiger schema is installed
scott@vsb>
scott@vsb>CREATE OR REPLACE TYPE phone_type AS VARRAY(10) OF NUMBER(6);
  2  /

Type created.

scott@vsb>CREATE TABLE myemp AS SELECT empno, ename FROM emp;

Table created.

scott@vsb>ALTER TABLE myemp ADD phones PHONE_TYPE;

Table altered.

scott@vsb>DESC myemp;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                 NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 PHONES                                                         PHONE_TYPE

scott@vsb>COL phones FORMAT a30;
scott@vsb>SELECT * FROM myemp;

     EMPNO ENAME      PHONES
---------- ---------- ------------------------------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

scott@vsb>
scott@vsb>UPDATE myemp SET phones=phone_type(123, 456) WHERE empno=7369;

1 row updated.

scott@vsb>SELECT * FROM myemp where empno=7369;

     EMPNO ENAME      PHONES
---------- ---------- ------------------------------
      7369 SMITH      PHONE_TYPE(123, 456)

scott@vsb>
scott@vsb>REM this is what we need to prevent, no duplicates
scott@vsb>UPDATE myemp SET phones=phone_type(123, 456, 123) WHERE empno=7369;

1 row updated.

scott@vsb>SELECT * FROM myemp where empno=7369;

     EMPNO ENAME      PHONES
---------- ---------- ------------------------------
      7369 SMITH      PHONE_TYPE(123, 456, 123)

scott@vsb>
scott@vsb>
scott@vsb>REM some examples on how to get VARRAYs into rows
scott@vsb>SELECT e.empno, p.column_value, COUNT(p.column_value)
  2  FROM myemp e, TABLE(e.phones) p
  3  WHERE e.empno=7369
  4  GROUP BY e.empno, p.column_value;

     EMPNO COLUMN_VALUE COUNT(P.COLUMN_VALUE)
---------- ------------ ---------------------
      7369          123                     2
      7369          456                     1

scott@vsb>
scott@vsb>SELECT e.empno, p.column_value, COUNT(p.column_value)
  2  FROM myemp e, TABLE(e.phones) p
  3  WHERE e.empno=7369
  4  GROUP BY e.empno, p.column_value
  5  HAVING COUNT(p.column_value) > 1;

     EMPNO COLUMN_VALUE COUNT(P.COLUMN_VALUE)
---------- ------------ ---------------------
      7369          123                     2

scott@vsb>
scott@vsb>REM set it to a proper value back
scott@vsb>UPDATE myemp SET phones=phone_type(123, 456) WHERE empno=7369;

1 row updated.

scott@vsb>
scott@vsb>SELECT * FROM myemp where empno=7369;

     EMPNO ENAME      PHONES
---------- ---------- ------------------------------
      7369 SMITH      PHONE_TYPE(123, 456)

scott@vsb>
scott@vsb>CREATE OR REPLACE TRIGGER myemp_upd_varray_trigger
  2  AFTER UPDATE ON myemp FOR EACH ROW
  3  DECLARE
  4  	 l_phones phone_type;
  5  BEGIN
  6  	 l_phones := :NEW.phones;
  7  	 --Now check if the array has duplicates
  8  	 --if at all the query returns something,
  9  	 --it indicates there is a duplicate
 10  	 --we don’t care which is the duplicate
 11  	 FOR x IN (
 12  	     SELECT p.COLUMN_VALUE, COUNT(p.COLUMN_VALUE)
 13  	     FROM DUAL d, TABLE(l_phones) p
 14  	     GROUP BY p.column_value
 15  	     HAVING COUNT(p.column_value) > 1) LOOP
 16  		 RAISE PROGRAM_ERROR;
 17  	 END LOOP;
 18  END;
 19  /

Trigger created.

scott@vsb>
scott@vsb>REM test the trigger; it should generate an error
scott@vsb>UPDATE myemp SET phones=phone_type(123, 456, 123) WHERE empno=7369;
UPDATE myemp SET phones=phone_type(123, 456, 123) WHERE empno=7369
       *
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "SCOTT.MYEMP_UPD_VARRAY_TRIGGER", line 14
ORA-04088: error during execution of trigger 'SCOTT.MYEMP_UPD_VARRAY_TRIGGER'


scott@vsb>
scott@vsb>REM create similar trigger for insert
scott@vsb>
scott@vsb>REM cleanup samples
scott@vsb>DROP TRIGGER myemp_upd_varray_trigger;

Trigger dropped.

scott@vsb>DROP TABLE myemp;

Table dropped.

scott@vsb>DROP TYPE phone_type;

Type dropped.

The SQL without the output from SQL*Plus is below.

REM assumes sample scott/tiger schema is installed CREATE OR REPLACE TYPE phone_type AS VARRAY(10) OF NUMBER(6); / CREATE TABLE myemp AS SELECT empno, ename FROM emp; ALTER TABLE myemp ADD phones PHONE_TYPE; DESC myemp; COL phones FORMAT a30; SELECT * FROM myemp; UPDATE myemp SET phones=phone_type(123, 456) WHERE empno=7369; SELECT * FROM myemp where empno=7369; REM this is what we need to prevent, no duplicates UPDATE myemp SET phones=phone_type(123, 456, 123) WHERE empno=7369; SELECT * FROM myemp where empno=7369; REM some examples on how to get VARRAYs into rows SELECT e.empno, p.column_value, COUNT(p.column_value) FROM myemp e, TABLE(e.phones) p WHERE e.empno=7369 GROUP BY e.empno, p.column_value; SELECT e.empno, p.column_value, COUNT(p.column_value) FROM myemp e, TABLE(e.phones) p WHERE e.empno=7369 GROUP BY e.empno, p.column_value HAVING COUNT(p.column_value) > 1; REM set it to a proper value back UPDATE myemp SET phones=phone_type(123, 456) WHERE empno=7369; SELECT * FROM myemp where empno=7369; CREATE OR REPLACE TRIGGER myemp_upd_varray_trigger AFTER UPDATE ON myemp FOR EACH ROW DECLARE l_phones phone_type; BEGIN l_phones := :NEW.phones; --Now check if the array has duplicates --if at all the query returns something, --it indicates there is a duplicate --we don’t care which is the duplicate FOR x IN ( SELECT p.COLUMN_VALUE, COUNT(p.COLUMN_VALUE) FROM DUAL d, TABLE(l_phones) p GROUP BY p.column_value HAVING COUNT(p.column_value) > 1) LOOP RAISE PROGRAM_ERROR; END LOOP; END; / REM test the trigger; it should generate an error UPDATE myemp SET phones=phone_type(123, 456, 123) WHERE empno=7369; REM create similar trigger for insert REM cleanup samples DROP TRIGGER myemp_upd_varray_trigger; DROP TABLE myemp; DROP TYPE phone_type;

I still don’t think storing collections as columns is in tune with relational database practice. It can cause proliferation of spreadsheet designs. Yet, it helps some times.

1. I'm with you, I'd much rather normalise my data model and let simple referential integrity take care of it.

   But these features tend to be very popular with Java (or other OO) programmers who "just want to store my objects in the database".

   They can also be useful for quick and dirty solutions but the convenience of storing all of your data in one record is, I think, a bit of a false economy.

   Then again, I am an old database head so what do I know ;-)

   Posted by: Andy Todd on January 4, 2004 05:09 PM