RE: non unique Index and Primary Key on same column. Is it needed?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 24 Aug 2004 11:14:52 -0700
Another difference concerning the uniqueness of an index enforcing a
primary key or unique constraint:
Let us suppose that you create the constraint with the enable novalidate
option, or change a constraint from disable to enable novalidate, on a
table that already has data (e.g. after a data load). Existing rows are
not checked to see if they satisfy the constraint.
If the index enforcing a primary key constraint is unique, then you
might have null values in the pk columns but no duplicate values.
If the index enforcing the primary key constraint is not unique, then
you might have null values in the pk columns and/or duplicate values.
-----Original Message-----
Bobak, Mark
Er, of course, that first sentence should read:
"At primary key creation time, if an index w/ the appropriate
column(s)=20
already exists, Oracle will utilize it, rather than creating a redundant
=
index."
Also, while I'm on the subject, I'll also add a couple of other points:
1.) If you have a multi-column index, Oracle may also be able to =
utilize
that for primary key enforcement. For example, if you created an index
=
on
(MACHINE_KEY,USABILITY_SETTINGS_KEY), that index may also be used for =
the=20
primary key enforcement, because the leading edge matches the key. If =
the
index was on (USABILITY_SETTINGS_KEY, MACHINE_KEY) that index could not
=
be
used to enforce a PK on MACHINE_KEY. =20
2.) I know I had one other point to make....but now it escapes me....
It's late and I need sleep. Hopefully a more alert person will fill in
whatever blank I'm leaving behind....;-)
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
