Oracle contrainte unique et unique indice
Quelqu'un pourrait-il clarifier à quoi sert un index unique sans contrainte unique (Oracle)? Par exemple,
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
jusqu'à présent, il semble qu'il y est une contrainte. Mais
create table test33(id int not null primary key,
test22_id int not null,
foreign key(test22_id) references test22(id) );
ne "ORA-02270: no matching unique or primary key for this column-list".
Je suis complètement confus par ce comportement. Est-il une contrainte ou pas?
il y a de nombreux articles qui expliquent pourquoi il est possible d'avoir une contrainte unique sans index unique; c'est clair et logique. Cependant, Je ne pas comprendre la raison de l'index unique sans contrainte.
2 réponses
une contrainte et un index sont des entités logiques distinctes. Une contrainte unique, par exemple, est visible dans USER_CONSTRAINTS (ou ALL_CONSTRAINTS ou DBA_CONSTRAINTS). Un index est visible dans USER_INDEXES (ou ALL_INDEXES ou DBA_INDEXES).
une contrainte unique est imposée par un index bien qu'il soit possible (et parfois nécessaire) d'imposer une contrainte unique en utilisant un index non unique. Une contrainte unique déférable, par exemple, est imposée à l'aide d'un indice non unique. Si vous créez un index non unique sur une colonne et ensuite créer une contrainte unique, vous pouvez également utiliser cet index non-unique pour appliquer la contrainte unique.
Dans la pratique, un index unique agit comme un unique, non reportable et les contraintes qu'il génère la même erreur qu'une contrainte unique soulève depuis la mise en œuvre de contraintes uniques utilise l'index. Mais ce n'est pas tout à fait la même chose parce qu'il n'y a pas de contrainte. Donc, comme vous l'avez vu, il n'y a pas de contrainte unique donc vous ne pouvez pas créer une contrainte clé étrangère qui renvoie à la colonne.
il y a des cas où vous pouvez créer un index unique que vous ne pouvez pas créer une contrainte unique. Un index basé sur la fonction, par exemple, qui renforce l'unicité conditionnelle. Si je voulais créer une table qui supporte les suppressions logiques mais s'assurer que COL1 est unique pour toutes les lignes supprimées
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.
mais s'il s'agit d'un indice simple et unique basé sur une non-Fonction, Il y en a probablement relativement peu cas où il est plus logique de créer l'indice que de créer la contrainte. D'autre part, il y a relativement peu de cas où il fait beaucoup de différence dans la pratique. Vous ne voudriez presque jamais déclarer une contrainte clé étrangère qui fait référence à une contrainte unique plutôt qu'une contrainte clé primaire donc vous perdez rarement quelque chose en créant seulement l'index et en ne créant pas la contrainte.
un Autre point qui peut être utile dans ce contexte est : Désactivation / suppression d'une contrainte unique existante ne supprimez pas l'indice unique sous-jacent. Vous devez supprimer l'index unique explicitement.