Oracle-L: RE: Syntax for foreign key

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 27 Mar 2003 10:43:49 -0800
Message-ID: <F001.0057481C.20030327104349@fatcity.com>

(see answer below)

> -----Original Message-----
> From: Robson, Peter [mailto:pgro_at_bgs.ac.uk]
>
> -----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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Mar 27 2003 - 12:43:49 CST