restriccion - Restricción única de Oracle e índice único

restricciones pl sql

Question

¿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 restricció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 una restricción o no?

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

Answer 1

Otro punto que puede ser útil en este contexto es: Deshabilitar / Soltar una restricción única existente no descarta el índice único subyacente. Tienes que soltar el índice único explícitamente.

Answer 2

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 ).

El índice aplica una restricción única, aunque es posible (y algunas veces es necesario) imponer una restricción única utilizando un índice no exclusivo. Una restricción única diferible, por ejemplo, se impone utilizando un índice no único. Si crea un índice no único en una columna y posteriormente crea una restricción única, también puede usar ese índice no exclusivo para imponer la restricción única.

En la práctica, un índice único actúa de manera muy similar a una restricción única, no diferible, ya que genera el mismo error que una restricción única que se plantea ya que la implementación de restricciones únicas usa el índice. Pero no es lo mismo porque no hay restricción. Entonces, como ha visto, no existe una restricción única, por lo que no puede crear una restricción de clave externa que haga referencia a la columna.

Hay casos donde puede crear un índice único que no puede crear una restricción única. Un índice basado en función, por ejemplo, que impone la singularidad condicional. Si quería crear una tabla que COL1 eliminaciones lógicas, pero asegúrese de que COL1 sea ​​único para todas las filas no eliminadas

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 directo no basado en funciones únicas, es probable que haya relativamente pocos casos en los que realmente tenga 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 desearía declarar una restricción de clave externa que hiciera referencia a una restricción única en lugar de una restricción de clave principal, por lo que rara vez pierde algo solo creando el índice y sin crear la restricción.