set echo on
set feedback on
/* logging */
spool test_3870.log

/* define index tablespace. change this for your needs */
define IXTBS='ICINGAWEB_IDX1';

/* 
drop existing constraint and data if any 
ignore ORA-02443 nonexistent constraint warning!
*/
alter table nsm_user_preference drop constraint nsm_user_pref_userid_key_uq drop index;
truncate table nsm_user_preference;

/* insert duplicates */
INSERT INTO nsm_user_preference (upref_user_id, upref_key, upref_val) VALUES
(1, 'org.icinga.ext.appstate', '{}');
INSERT INTO nsm_user_preference (upref_user_id, upref_key, upref_val) VALUES
(1, 'org.icinga.ext.appstate', '{}');
INSERT INTO nsm_user_preference (upref_user_id, upref_key, upref_val) VALUES
(1, 'org.icinga.ext.appstate', '{}');
INSERT INTO nsm_user_preference (upref_user_id, upref_key, upref_val) VALUES
(1, 'org.icinga.ext.appstate', '{}');
INSERT INTO nsm_user_preference (upref_user_id, upref_key, upref_val) VALUES
(1, 'org.icinga.ext.appstate', '{}');
INSERT INTO nsm_user_preference (upref_user_id, upref_key, upref_val) VALUES
(1, 'org.icinga.ext.appstate2', '{}');
commit;

/* count records , should be 6 on a previous empty table*/
select count(*) from nsm_user_preference;

/* try to enable constraint -- should fail */
alter table nsm_user_preference add constraint nsm_user_pref_userid_key_uq UNIQUE (upref_user_id, upref_key)
  using index tablespace &IXTBS;

/* run update script */
@oracle_v1-8-0_to_v1-8-3.sql

/* count records  again, should be 2 because of 2 keys */
select count(*) from nsm_user_preference;

spool off;
exit;
