Oracle Tips and Tricks — David Fitzjarrell

May 30, 2013

We Have No Constraints

Filed under: General — dfitzjarrell @ 12:53

A question was recently asked by a colleague regarding unique indexes and constraints. He was complaining that the unique index did not create the associated constraint by default. Unlike the situation when a unique constraint is created (the associated unique index is created if no other index is specified) there really isn’t any reason for Oracle to create a unique constraint for a given unique index. Also, it’s possible to create a unique constraint which uses a non-unique index. Let’s explore this further.

Whether you have a unique index or a unique constraint the following error message will appear if you violate the uniqueness:

ORA-00001: unique constraint … violated

The confusing part is probably the report by Oracle that a unique constraint has been violated. This message can report the constraint name (for unique and primary key constraints) or the index name (for unique indexes in the absence of a constraint). The first act a DBA would likely perform is a check of the DBA_CONSTRAINTS view to search for the reported constraint; since the index name is reported rather than the constraint name no such constraint exists. Hopefully the DBA would then go to DBA_INDEXES to search for a unique index that may have been created to enforce uniqueness, which is what this colleague did. Let’s go through three scenarios that may help explain why, when a unique index is created, the associated unique constraint is not.

Let’s start with the warhorse of the Oracle demo tables, EMP. We create the demo tables then add a unique index to EMP:


SQL> --
SQL> -- Make the empno column unique
SQL> --
SQL> -- Use an index
SQL> --
SQL> -- The expected constraint does not
SQL> -- get created by default
SQL> --
SQL>
SQL> create unique index emp_uq_empno on emp(empno);

Index created.

SQL>
SQL> select index_name, uniqueness
  2  from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_UQ_EMPNO                   UNIQUE

SQL>
SQL> select owner, table_name, constraint_name, index_name
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
BING                           EMP                            SYS_C007995

SQL>

The constraint reported is a NOT NULL constraint for the EMPNO column, it has nothing to do with the unique index. Notice that no UNIQUE constraint exists. So let’s now violate that unique index and see what Oracle reports:


SQL> --
SQL> -- Violate the uniqueness
SQL> --
SQL> -- Oracle reports a constraint violation
SQL> -- even though no actual constraint
SQL> -- exists
SQL> --
SQL>
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
*
ERROR at line 1:
ORA-00001: unique constraint (BING.EMP_UQ_EMPNO) violated


SQL>

As was noted earlier Oracle reports that a constraint was violated yet reports the name for the unique index created against the EMP table. Let’s change this around a bit and create an actual UNIQUE constraint and see what happens:


SQL> --
SQL> -- Drop the index
SQL> --
SQL> -- Create a unique constraint now
SQL> --
SQL> -- Notice that the index IS created
SQL> --
SQL>
SQL> drop index emp_uq_empno;

Index dropped.

SQL>
SQL> alter table emp
  2  add constraint emp_uq unique(empno);

Table altered.

SQL>
SQL> select index_name, uniqueness
  2  from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_UQ                         UNIQUE

SQL>
SQL> select owner, table_name, constraint_name, index_name
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
BING                           EMP                            EMP_UQ                         EMP_UQ
BING                           EMP                            SYS_C007995

SQL>

Now we have both a UNIQUE constraint and a unique index. Violating this reports what we would expect to see from Oracle:


QL> --
SQL> -- Violate the uniqueness
SQL> --
SQL> -- Oracle reports a constraint violation
SQL> --
SQL>
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
*
ERROR at line 1:
ORA-00001: unique constraint (BING.EMP_UQ) violated


SQL>

The error message reports the constraint name that was violated and, as shown, we do have a UNIQUE constraint by that name AND an associated UNIQUE index to go along with it. Oracle correctly assumes that since you want a UNIQUE constraint you also want a UNIQUE index. But it can’t assume that if you create a UNIQUE index you also want a UNIQUE constraint because you can build a UNIQUE constraint with a non-unique index. We drop the existing constraint and start again:


SQL> --
SQL> -- NOW create a unique constraint
SQL> -- that uses a non-unique index
SQL> --
SQL>
SQL> alter table emp drop constraint emp_uq;

Table altered.

SQL>
SQL> create index emp_empno on emp(empno);

Index created.

SQL>
SQL> alter table emp add constraint emp_uq
  2  unique (empno)
  3  using index emp_empno;

Table altered.

SQL>
SQL> select index_name, uniqueness
  2  from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_EMPNO                      NONUNIQUE

SQL>
SQL> select owner, table_name, constraint_name, index_name
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
BING                           EMP                            EMP_UQ                         EMP_EMPNO
BING                           EMP                            SYS_C007995

SQL>

Once we dropped the UNIQUE constraint the UNIQUE index created as part of that process was also dropped. Now the only index we have on EMP is the non-unique index on EMPNO we created before creating the constraint. Take note that we used the USING INDEX clause of the ADD CONSTRAINT action from ALTER TABLE, to tell Oracle that we have an index we want to use so don’t create the default index. Again let’s violate the uniqueness:


SQL> --
SQL> -- Violate the uniqueness
SQL> --
SQL> -- Oracle reports a constraint violation
SQL> --
SQL>
SQL> insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  2  values (7369, 'BORPO','FLERB',7844,sysdate-121, 900, 0, 20);
insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
*
ERROR at line 1:
ORA-00001: unique constraint (BING.EMP_UQ) violated


SQL>

Here is the interesting part of using a non-unique index to enforce a UNIQUE/PRIMARY KEY constraint: if you drop the constraint the index remains:


SQL> --
SQL> -- Drop the constraint
SQL> --
SQL> -- The index remains
SQL> --
SQL>
SQL> alter table emp drop constraint emp_uq;

Table altered.

SQL>
SQL> select index_name, uniqueness
  2  from user_indexes;

INDEX_NAME                     UNIQUENES
------------------------------ ---------
EMP_EMPNO                      NONUNIQUE

SQL>
SQL> select owner, table_name, constraint_name, index_name
  2  from user_constraints;

OWNER                          TABLE_NAME                     CONSTRAINT_NAME                INDEX_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
BING                           EMP                            SYS_C007998

SQL>

Since there are several ways to implement uniqueness in a column or set of columns in a table, one being to simply create a unique index on the key column or columns, it isn’t necessary to have an associated UNIQUE constraint in force. It may be confusing to not have such a constraint (and I prefer to have such a constraint in place to enforce uniqueness) but it’s not required. As to why Oracle doesn’t automatically create such a constraint when a unique index is created, well, I think it has to do with how that constraint can be created. We can allow Oracle to create the unique index, we can use a non-unique index that already exists, or we could use a separate, existing unique index to keep the number of indexes manageable.

Yes, Oracle could modify the CREATE INDEX statement yet again to include a CONSTRAINT clause but I think that would be one more thing to forget or misuse — not every index needs a constraint connected to it. And how would you police how that parameter is used? UNIQUE and PRIMARY KEY constraints would be logical to create for UNIQUE indexes; maybe the CONSTRAINT option would only be available for CREATE UNIQUE INDEX statements. Again, just because you have a UNIQUE index doesn’t mean you need to have a corresponding UNIQUE constraint.

That’s my story and I’m sticking with it.

About these ads

5 Comments »

  1. I think, you can have an index over expressions that calculate a virtual column, which is not legal for “simple” unique constraints, that only accept columns, right?

    Comment by Holger — August 19, 2013 @ 04:59 | Reply

    • That is incorrect, as in 11.2.0.3 you can build a unique constraint on a virtual column:

      SQL> CREATE TABLE EMP
        2         (EMPNO NUMBER(4) NOT NULL,
        3          ENAME VARCHAR2(10),
        4          JOB VARCHAR2(9),
        5          MGR NUMBER(4),
        6          HIREDATE DATE,
        7          SAL NUMBER(7, 2),
        8          COMM NUMBER(7, 2),
        9          DEPTNO NUMBER(2),
       10          TTL_COMP number generated always as (sal + nvl(comm, 0)) virtual )
       11  partition by range(ttl_comp)
       12  (partition e1 values less than (700),
       13   partition e2 values less than (1000),
       14   partition e3 values less than (1300),
       15   partition e4 values less than (1700),
       16   partition e5 values less than (2000),
       17   partition e6 values less than (3500),
       18   partition e7 values less than (6000),
       19   partition elast values less than (maxvalue));
      
      Table created.
      
      SQL>
      SQL> alter table emp add constraint emp_ttl_comp  unique(ttl_Comp);
      
      Table altered.
      
      SQL>

      AS you can see the constraint created without error.

      Comment by dfitzjarrell — August 19, 2013 @ 08:50 | Reply

      • Hello!

        For Table:

        create table I8_I18NSTRING (
        id number(10) primary key,
        EN varchar2(4000 char),
        ART number(10) not null
        );

        on our Database we have an index like this:

        create unique index I18N_UNIQUE_EN on I8_I18NSTRING
        (case when (ART=99 or EN is null) then null else TO_CHAR(ART)||’_’||LOWER(TRIM(EN)) end );

        But a corresponding unique constraint:

        alter table I8_I18NSTRING add constraint unique_test unique
        (case when (ART=99 or EN is null) then null else TO_CHAR(ART)||’_’||LOWER(TRIM(EN)) end );

        is rejected with ORA-00904: invalid identifier.

        Maybe I have to explicitly create that virtual column?
        But why can’t Oracle automatically create the virtual column for the constraint while it does so for the index?

        BTW this is 11.2.0.3.0

        Comment by Holger — August 21, 2013 @ 05:28

      • You cannot use CASE in an alter table statement (to my knowledge) even to create a virtual column; I succeeded with this:

        SQL> alter table I8_I18NSTRING add constraint unique_test unique
          2   (case when (ART=99 or EN is null) then null else TO_CHAR(ART)||'_'||LOWER(TRIM(EN)) end );
         (case when (ART=99 or EN is null) then null else TO_CHAR(ART)||'_'||LOWER(TRIM(EN)) end )
          *
        ERROR at line 2:
        ORA-00904: : invalid identifier
        
        
        SQL>
        SQL> alter table I8_I18NSTRING add (art_en generated always as (TO_CHAR(ART)||'_'||LOWER(TRIM(EN))));
        
        Table altered.
        
        SQL>
        SQL> desc I8_I18NSTRING
         Name                                      Null?    Type
         ----------------------------------------- -------- ----------------------------
         ID                                        NOT NULL NUMBER(10)
         EN                                                 VARCHAR2(4000 CHAR)
         ART                                       NOT NULL NUMBER(10)
         ART_EN                                             VARCHAR2(4000 CHAR)
        
        SQL>
        SQL> alter table I8_I18NSTRING add constraint unique_test unique(art_en);
        
        Table altered.
        
        SQL>
        SQL> select owner, constraint_name, constraint_type from user_constraints;
        
        OWNER                          CONSTRAINT_NAME                C
        ------------------------------ ------------------------------ -
        GRIBNAUT                       UNIQUE_TEST                    U
        GRIBNAUT                       SYS_C0010660                   P
        GRIBNAUT                       SYS_C0010659                   C
        
        SQL>

        So, yes, you need to create the virtual column the constraint is to reference.

        Comment by dfitzjarrell — August 21, 2013 @ 12:38

  2. > You cannot use CASE in an alter table statement (to my knowledge)

    or in my words

    > I think, you can have an index over expressions that calculate a virtual column, which is not legal for “simple” unique constraints

    I just wasn’t specific enough to reduce it to CASE …

    Let’s call it a draw ;-D

    Comment by Holger — August 22, 2013 @ 01:29 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 592 other followers

%d bloggers like this: