3 Replies Latest reply: Aug 11, 2012 1:44 PM by Mark D Powell RSS

    Constraint issue...

    jey84
      hai

      I am going to create unique constraint for the table named invoices. But that contains lots of duplicate records. Now my requirements is that constraint should validate only new record not old record.

      is it possible in oracle 9i R2?

      Thanks for your valuable response.
        • 1. Re: Constraint issue...
          saurabh
          A unique constraint will be created on a column of the table invoice. If that colum doesnot have a duplicate values then it will allow you to create the constraints.
          • 2. Re: Constraint issue...
            Khayyam
            Unique constraint implicitly creates Unique Index. If you want to add and validate that constraints your columns shouldnt have dublicate values... Your problem is bad design...

            However, you can update old dublicate values set them NULL and constraint creation/validation will be successful.

            You can set them NULL by
            DECLARE
               v_id   NUMBER;
            BEGIN
               FOR a IN (SELECT ROWID, id FROM test)
               LOOP
                  BEGIN
                     SELECT id
                       INTO v_id
                       FROM test
                      WHERE id = a.id AND ROWID != a.ROWID;
            
                     IF v_id IS NOT NULL
                     THEN
                        UPDATE test
                           SET id = NULL
                         WHERE ROWID = a.ROWID;
                         COMMIT;
                     END IF;
                  EXCEPTION
                     WHEN NO_DATA_FOUND
                     THEN
                        NULL;
                  END;
               END LOOP;
            END;
            SQL> create table test (id number);
            
            Table created.
            
            SQL> insert into test values(1);
            
            1 row created.
            
            SQL> insert into test values(1);
            
            1 row created.
            
            SQL> insert into test values(2);
            
            1 row created.
            
            SQL> select id from test;
            
                    ID
            ----------
                     1
                     1
                     2
            
            SQL> alter table test add constraint id_un unique(id);
            alter table test add constraint id_un unique(id)
                                            *
            ERROR at line 1:
            ORA-02299: cannot validate (HR.ID_UN) - duplicate keys found
            
            
            SQL> DECLARE
              2     v_id   NUMBER;
              3  BEGIN
              4     FOR a IN (SELECT ROWID, id FROM test)
              5     LOOP
              6        BEGIN
              7           SELECT id
              8             INTO v_id
              9             FROM test
             10            WHERE id = a.id AND ROWID != a.ROWID;
             11
             12           IF v_id IS NOT NULL
             13           THEN
             14              UPDATE test
             15                 SET id = NULL
             16               WHERE ROWID = a.ROWID;
             17           END IF;
             18        EXCEPTION
             19           WHEN NO_DATA_FOUND
             20           THEN
             21              NULL;
             22        END;
             23     END LOOP;
             24  END;
             25  /
            
            PL/SQL procedure successfully completed.
            
            SQL> select id from test;
            
                    ID
            ----------
            
                     1
                     2
            
            SQL> alter table test add constraint id_un unique(id);
            
            Table altered.
            
            SQL> insert into test values(1);
            insert into test values(1)
            *
            ERROR at line 1:
            ORA-00001: unique constraint (HR.ID_UN) violated
            
            
            SQL>
            • 3. Re: Constraint issue...
              Mark D Powell
              If you need to place a PK or UK constraint on column or columns that contain duplicate data then Oracle does support a way to do this. The easiest way is to first create a non-unique index on the column(s) in question then alter the table adding the constraint using the existin non-unique index with the DEFERRED and NOVALIDATE clauses.

              See the SQL manual for the syntax.

              HTH -- Mark D Powell --