40 votos

Restricción única de Oracle y pregunta de índice única

¿Podría alguien aclarar cuál es el propósito de tener un índice único sin restricción única (Oracle)? Por ejemplo,

create table test22(id int , id1 int, tmp varchar(20));
create unique index idx_test22 on test22(id);
insert into test22(id, id1,tmp) values (1,2,'aaa'); // ok
insert into test22(id, id1,tmp) values (1,2,'aaa'); // fails, ORA-00001: unique   
  // constraint (TEST.IDX_TEST22) violated

Hasta ahora parece que hay una limitación. Pero

create table test33(id int not null primary key, 
test22_id int not null, 
foreign key(test22_id) references test22(id) );

también falla con "ORA-02270: no matching unique or primary key for this column-list" . Estoy totalmente confundido por este comportamiento. ¿Hay alguna restricción o no?

Hay muchos artículos que explican por qué es posible tener una restricción única sin un índice único; eso está claro y tiene perfecto sentido. Sin embargo, no entiendo la razón de un índice único sin restricción.

Gracias por su ayuda.

52voto

Justin Cave Puntos 114578

Una restricción y un índice son entidades lógicas separadas. Una restricción única, por ejemplo, es visible en USER_CONSTRAINTS (o ALL_CONSTRAINTS o DBA_CONSTRAINTS ). Un índice es visible en USER_INDEXES (o ALL_INDEXES o DBA_INDEXES ).

Una restricción única se aplica mediante un índice, aunque es posible (y a veces necesario) aplicar una restricción única utilizando un índice no único. Una restricción única aplazable, por ejemplo, se aplica utilizando un índice no único. Si se crea un índice no único en una columna y posteriormente se crea una restricción única, también se puede utilizar ese índice no único para hacer cumplir la restricción única.

En la práctica, un índice único actúa de manera muy parecida a una restricción única e inaplazable en el sentido de que plantea el mismo error que una restricción única, ya que la aplicación de las restricciones únicas utiliza el índice. Pero no es exactamente lo mismo porque no hay ninguna restricción. Así que, como se ha visto, no hay una restricción única, por lo que no se puede crear una restricción de clave externa que haga referencia a la columna.

Hay casos en los que se puede crear un índice único que no se puede crear una restricción única. Un índice basado en una función, por ejemplo, que hace cumplir la unicidad condicional. Si quisiera crear una tabla que soportara eliminaciones lógicas pero que asegurara que COL1 es único para todas las filas no borradas

SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE t (
  2    col1 number,
  3    deleted_flag varchar2(1) check( deleted_flag in ('Y','N') )
  4* )
SQL> /

Table created.

SQL> create unique index idx_non_deleted
  2      on t( case when deleted_flag = 'N' then col1 else null end);

Index created.

SQL> insert into t values( 1, 'N' );

1 row created.

SQL> insert into t values( 1, 'N' );
insert into t values( 1, 'N' )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IDX_NON_DELETED) violated

SQL> insert into t values( 1, 'Y' );

1 row created.

SQL> insert into t values( 1, 'Y' );

1 row created.

Pero si estamos hablando de un índice recto y único basado en la no función, probablemente hay relativamente pocos casos en los que realmente tiene más sentido crear el índice en lugar de crear la restricción. Por otro lado, hay relativamente pocos casos en los que hace mucha diferencia en la práctica. Casi nunca se querría declarar una restricción de clave externa que hiciera referencia a una restricción única en lugar de una restricción de clave primaria, por lo que rara vez se pierde algo por crear sólo el índice y no por crear la restricción.

Iteramos.com

Iteramos es una comunidad de desarrolladores que busca expandir el conocimiento de la programación mas allá del inglés.
Tenemos una gran cantidad de contenido, y también puedes hacer tus propias preguntas o resolver las de los demás.

Powered by:

X