Contrainte FK et index de même nom

Version imprimable

Bonjour,

quelqu'un a t-il une explication sur le phénomène suivant ?

J'ai une table avec une contrainte de type FK et un index sur cette même table. L'index et la contrainte ont le même nom : CONTRAINTE2.

(vu le contexte, j'ai anonymisé les données )

Je supprime la contrainte, puis l'index, sans aucun souci.

Je ne peux pas re-créer l'index après la contrainte, car il me crache un

Code:

1
2
3
 
Erreur SQL : ORA-00955: ce nom d'objet existe déjà
00955. 00000 -  "name is already used by an existing object"

ci-joint, un reverse de la table obtenu avec Oracle SQL Developper.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
  CREATE TABLE "SCHEMA"."ATTRIBUT" 
   (	"CHAMP1" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"CHAMP2" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"CHAMP3" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"ORDRE" NUMBER(20,0) NOT NULL ENABLE, 
	"LG_MAX" NUMBER(4,0) NOT NULL ENABLE, 
	"LG_MIN" NUMBER(4,0), 
	"B_OBLIG" NUMBER(1,0) NOT NULL ENABLE, 
	"LIBELLE" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "PK_ATTRIBUT" PRIMARY KEY ("CHAMP1", "CHAMP2")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TBS_I"  ENABLE, 
	 CONSTRAINT "CONTRAINTE1" FOREIGN KEY ("CHAMP3")
	  REFERENCES "SCHEMA"."TYP_OBJET" ("CDE") ENABLE, 
	 CONSTRAINT "CONTRAINTE2" FOREIGN KEY ("CHAMP1")
	  REFERENCES "SCHEMA"."TY_TYPO" ("NOM_TYPO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TBS_D" ;
 
 
  CREATE INDEX "SCHEMA"."CONTRAINTE2" ON "SCHEMA"."ATTRIBUT" ("CHAMP1") 
	  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
	  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
	  TABLESPACE "TBS_I" ;

Je n'arrive pas à reproduire votre problème.
Testé sur une 11gR2 :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
create table t1
(
  id_t1 number(5) not null
, constraint pk_t1 primary key (id_t1) using index
);
-- Table created.
 
insert into t1 (id_t1) values (1);
-- 1 row(s) inserted.
 
create table t2
(
  id_t1 number(5) not null
, constraint fk_t2_t1 foreign key (id_t1) references t1 (id_t1)
);
-- Table created.
 
create index fk_t2_t1 on t2 (id_t1);
-- Index created.
 
insert into t2 (id_t1) values (1); -- 1 row(s) inserted.
insert into t2 (id_t1) values (2); -- ORA-02291: integrity constraint (FK_T2_T1) violated - parent key not found
 
alter table t2 drop constraint fk_t2_t1;
-- Table altered.
 
drop index fk_t2_t1;
-- Index dropped.
 
alter table t2 add constraint fk_t2_t1 foreign key (id_t1) references t1 (id_t1);
-- Table altered.
 
create index fk_t2_t1 on t2 (id_t1);
-- Index created.

Ceci fonctionne pour moi.
Version :11.2.0.3

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
 
create table TYP_OBJET (CDE VARCHAR2(20 BYTE) primary key);
 
create table TY_TYPO (NOM_TYPO VARCHAR2(20 BYTE) primary key);
 
 
CREATE TABLE "ATTRIBUT" 
   (	"CHAMP1" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"CHAMP2" VARCHAR2(10 BYTE) NOT NULL ENABLE, 
	"CHAMP3" VARCHAR2(20 BYTE) NOT NULL ENABLE, 
	"ORDRE" NUMBER(20,0) NOT NULL ENABLE, 
	"LG_MAX" NUMBER(4,0) NOT NULL ENABLE, 
	"LG_MIN" NUMBER(4,0), 
	"B_OBLIG" NUMBER(1,0) NOT NULL ENABLE, 
	"LIBELLE" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "PK_ATTRIBUT" PRIMARY KEY ("CHAMP1", "CHAMP2")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    ENABLE, 
	 CONSTRAINT "CONTRAINTE1" FOREIGN KEY ("CHAMP3")
	  REFERENCES TYP_OBJET ("CDE") ENABLE, 
	 CONSTRAINT "CONTRAINTE2" FOREIGN KEY ("CHAMP1")
	  REFERENCES TY_TYPO ("NOM_TYPO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) ;
 
 
  CREATE INDEX "CONTRAINTE2" ON "ATTRIBUT" ("CHAMP1") 
	  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
	  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
	  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT);

Quelle est votre version?
jko

11/01/2013, 09h01
Mohamed.Houri

Bonjour,

Est-ce que vous vous êtes posé une question sur l’utilité de cet indexe? Vous avez déjà un index sur la primary key (champ1, champ2). Pourquoi alors ajouter un autre indexe (champ1) déjà contenu dans le premier indexe? C’est un indexe redondant (FK et Indexe) . Quant à la menace de locks et deadlock introduite par la présence d’une FK non indexée dans un environnement OLTP, il est intéressant de rappeler que l’indexe de la clé primaire joue ICI dans ce cas parfaitement ce role. Nul besoin donc d’ajouter encore un autre indexe à votre table.

Il arrive parfois, pour des raisons de clustering factor et donc de désirabilité de l’index par le CBO, que l’index (champ1) s’impose malgré la présence de l’index (champ1, champ2). Mais au stade où vous en êtes (implémentation du modèle de donnée je suppose) il est encore trop tôt pour penser à ce genre de situations.

Edit : ajout de ICI dans ce cas
11/01/2013, 09h46
jkofr

Citation:

Envoyé par Mohamed.Houri

Quant à la menace de locks et deadlock introduite par la présence d’une FK non indexée dans un environnement OLTP, il est intéressant de rappeler que l’indexe de la clé primaire joue parfaitement ce role. .

Hello,

Une collègue fréquentant ce forum a écrit un article interessant:

http://blog.developpez.com/pachot/p1...les_etrangeres

Bonne lecture
jko