Jacques Kilchoer
Thu, 27 Mar 2003 11:03:24 -0800
(see answer below)
> -----Original Message-----
> From: Robson, Peter [mailto:[EMAIL PROTECTED]]
>
> -----Original Message-----
>
> Indexes are only automatically created to enforce primary key
> and unique
> constraints.
> If you want an index to match the columns of a foreign key
> constraint, you
> will have to do it separately.
> P.S. If you already have a non-unique index on the PK
> [Peter Robson]
>
> Not possible - PK indexes are unique by definition.
>
> peter
>
>
> or unique constraint column(s), then Oracle will use that
> one to enforce
> the PK or unique constraint.
OK, I probably didn't phrase it very clearly. Let me try again:
If you already have a non-unique index on a set of columns, and then create a primary key (or unique) constraint on those columns, Oracle will use the non-unique index to enforce the primary key (or unique) constraint.
See Proof of concept below.
SQL> create table rock_group
2 (group_name varchar2 (30),
3 group_country varchar2 (40),
4 group_founded date,
5 discography_id number,
6 group_dissolved date
7 ) ;
Table creee.
SQL> create index rock_group_idx1
2 on rock_group (group_name, group_country, group_founded) ;
Index cree.
SQL> alter table rock_group
2 add (constraint rock_group_pk
3 primary key (group_name, group_country, group_founded)) ;
Table modifiee.
SQL> select constraint_name, constraint_type, status
2 from user_constraints
3 where table_name = 'ROCK_GROUP' ;
CONSTRAINT_NAME C STATUS
------------------------------ - --------
ROCK_GROUP_PK P ENABLED
SQL> select a.object_id, b.index_name, b.uniqueness
2 from user_objects a, user_indexes b
3 where
4 b.table_name = 'ROCK_GROUP'
5 and b.index_name = a.object_name
6 and a.object_type = 'INDEX' ;
OBJECT_ID INDEX_NAME UNIQUENES
--------- ------------------------------ ---------
357425 ROCK_GROUP_IDX1 NONUNIQUE
SQL> select
2 b.name as constraint_name, c.enabled as enforcing_index_object_id
3 from
4 dba_users a, sys.con$ b, sys.cdef$ c
5 where
6 a.username = user
7 and a.user_id = b.owner#
8 and b.name = 'ROCK_GROUP_PK'
9 and b.con# = c.con# ;
CONSTRAINT_NAME ENFORCING_INDEX_OBJECT_ID
------------------------------ -------------------------
ROCK_GROUP_PK 357425