4 Replies Latest reply: Dec 29, 2007 6:37 AM by 426850 RSS

    ORA-14196 on ALTER TABLE, ADD CONSTRAINT, PRIMARY KEY, USING option

    426850
      Below example is from Oracle® Database Administrator's Guide 10g Release 2 (10.2) - Chapter 16 Managing Indexes
      http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#i1006566

      but the example code fails with ORA-14196;
      Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
      Connected as hr
      
      DROP TABLE c PURGE ;
      CREATE TABLE c(c1 INT, c2 INT);
      CREATE UNIQUE INDEX ci ON c (c1, c2);
      ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
      
      ORA-14196: Specified index cannot be used to enforce the constraint.
      
      DROP TABLE c PURGE ;
      CREATE TABLE c(c1 INT, c2 INT);
      CREATE UNIQUE INDEX ci ON c (c1);
      ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1, c2) USING INDEX ci;
      
      ORA-14196: Specified index cannot be used to enforce the constraint.
      
      DROP TABLE c PURGE ;
      CREATE TABLE c(c1 INT, c2 INT);
      CREATE UNIQUE INDEX ci ON c (c1, c2);
      ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1);
      SELECT index_name FROM user_indexes WHERE table_name = 'C';
      
      INDEX_NAME
      ------------------------------
      CPK
      CI
      Other than having two different indexes can there be any workarounds for ORA-14196 here? Because this is a need on a heavy insert/update table.

      Best regards.
        • 1. Re: ORA-14196 on ALTER TABLE, ADD CONSTRAINT, PRIMARY KEY, USING option
          561093
          Hi,
          DROP TABLE c PURGE ;
          CREATE TABLE c(c1 INT, c2 INT);
          CREATE UNIQUE INDEX ci ON c (c1, c2);
          ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
          The error is correctly raised.

          You have created a Unique index on C1 & C2. Example values of these columns could be:
          SQL> select * from c;

                  C1         C2
          ---------- ----------
                   1          1
                   1          2
                   1          3
          ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
          With this statement you would like to create a PK only on C1 by using the same index. If you look at the values of column "C1", they are duplicate. Hence, you may not use this index.


          Instead create a new index.

          Regards
          • 2. Re: ORA-14196 on ALTER TABLE, ADD CONSTRAINT, PRIMARY KEY, USING option
            426850
            you are right Citrus thank you, for below case even using USING INDEX syntax in not necessary, Oracle uses single index;
            DROP TABLE c PURGE ;
            CREATE TABLE c(c1 INT, c2 INT);
            CREATE UNIQUE INDEX ci ON c (c1, c2);
            ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1, c2) ;
            SELECT index_name FROM user_indexes WHERE table_name = 'C';
            
            INDEX_NAME
            ----------------------------
            CI
            my mistake, I tried testing the example-3 in the documentation with a UNIQUE index and got the error;
            DROP TABLE c PURGE ;
            CREATE TABLE c(c1 INT, c2 INT);
            CREATE INDEX ci ON c (c1, c2);
            ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
            SELECT index_name FROM user_indexes WHERE table_name = 'C';
            
            INDEX_NAME
            ------------------------------
            CI
            
            insert into c values (1,1);
            
            1 row inserted
            
            insert into c values (1,2);
            
            ORA-00001: unique constraint (HR.CPK) violated
            
            insert into c values (2,1);
            
            1 row inserted
            • 3. Re: ORA-14196 on ALTER TABLE, ADD CONSTRAINT, PRIMARY KEY, USING option
              544860
              Hey guys,

              what about this,

              DROP CLUSTER c;
              CREATE CLUSTER c(
              col NUMBER
              );

              CREATE INDEX ind_c ON CLUSTER c;

              DROP TABLE t PURGE;
              CREATE TABLE t(ID NUMBER, goman varchar2(100)) CLUSTER c(ID);

              ALTER TABLE t ADD CONSTRAINT con_t PRIMARY KEY(ID) USING INDEX ind_c;
              • 4. Re: ORA-14196 on ALTER TABLE, ADD CONSTRAINT, PRIMARY KEY, USING option
                426850
                this is another case, also for multi-table cluster by index an additional index for the primary key constraint is needed;
                SQL> -- http://psoug.org/reference/clusters.html
                SQL> set linesize 121
                SQL> show release
                Oracle Database 10g Express Edition Release 10.2.0.1.0 
                
                SQL> DROP TABLE cserv_inst PURGE ;
                
                Table dropped
                
                SQL> DROP TABLE cservers PURGE ;
                
                Table dropped
                
                SQL> DROP CLUSTER sc_srvr_id ;
                
                Cluster dropped
                
                SQL> CREATE CLUSTER sc_srvr_id ( srvr_id NUMBER(10) ) ;
                
                Cluster created
                
                SQL> SELECT cluster_name, hashkeys, single_table FROM user_clusters;
                
                CLUSTER_NAME                     HASHKEYS SINGLE_TABLE
                ------------------------------ ---------- --------------------
                SC_SRVR_ID                              0     N
                
                SQL> CREATE INDEX idx_sc_srvr_id ON CLUSTER sc_srvr_id;
                
                Index created
                
                SQL> SELECT index_name, index_type FROM user_indexes WHERE index_type = 'CLUSTER' ;
                
                INDEX_NAME                     INDEX_TYPE
                ------------------------------ ---------------------------
                IDX_SC_SRVR_ID                 CLUSTER
                
                SQL> CREATE TABLE cservers (
                  2  srvr_id    NUMBER(10),
                  3  network_id NUMBER(10),
                  4  status     VARCHAR2(1),
                  5  latitude   FLOAT(20),
                  6  longitude  FLOAT(20),
                  7  netaddress VARCHAR2(15))
                  8  CLUSTER sc_srvr_id (srvr_id);
                
                Table created
                
                SQL> CREATE TABLE cserv_inst (
                  2  siid          NUMBER(10),
                  3  si_status     VARCHAR2(15),
                  4  type          VARCHAR2(5),
                  5  installstatus VARCHAR2(1),
                  6  location_code NUMBER(10),
                  7  custacct_id   VARCHAR2(10),
                  8  srvr_id       NUMBER(10),
                  9  ws_id         NUMBER(10))
                 10  CLUSTER sc_srvr_id (srvr_id);
                
                Table created
                
                SQL> SELECT table_name, cluster_name FROM user_tables WHERE cluster_name IS NOT NULL;
                
                TABLE_NAME                     CLUSTER_NAME
                ------------------------------ ------------------------------
                CSERVERS                       SC_SRVR_ID
                CSERV_INST                     SC_SRVR_ID
                
                SQL> EXPLAIN PLAN FOR
                  2    SELECT srvr_id
                  3    FROM cservers s
                  4    WHERE EXISTS (
                  5      SELECT srvr_id
                  6      FROM cserv_inst i
                  7      WHERE s.srvr_id = i.srvr_id);
                
                Explained
                
                SQL> SELECT * FROM TABLE(dbms_xplan.display);
                
                PLAN_TABLE_OUTPUT
                --------------------------------------------------------------------------------
                Plan hash value: 3852057660
                --------------------------------------------------------------------------------
                | Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time
                --------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT      |            |     1 |    26 |     3   (0)| 00:00:
                |   1 |  NESTED LOOPS SEMI    |            |     1 |    26 |     3   (0)| 00:00:
                |   2 |   TABLE ACCESS FULL   | CSERVERS   |     1 |    13 |     2   (0)| 00:00:
                |*  3 |   TABLE ACCESS CLUSTER| CSERV_INST |     1 |    13 |     1   (0)| 00:00:
                --------------------------------------------------------------------------------
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   3 - filter("S"."SRVR_ID"="I"."SRVR_ID")
                Note
                -----
                   - dynamic sampling used for this statement
                
                19 rows selected
                
                SQL> ALTER TABLE cservers ADD CONSTRAINT pk_cserv_inst PRIMARY KEY(srvr_id) USING INDEX idx_sc_srvr_id;
                
                ALTER TABLE cservers ADD CONSTRAINT pk_cserv_inst PRIMARY KEY(srvr_id) USING INDEX idx_sc_srvr_id
                
                ORA-14196: Specified index cannot be used to enforce the constraint.
                
                SQL> ALTER TABLE cservers ADD CONSTRAINT pk_cserv_inst PRIMARY KEY(srvr_id) ;
                
                Table altered