Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: non unique Index and Primary Key on same column. Is it needed?

RE: non unique Index and Primary Key on same column. Is it needed?

From: TOMPKINS, MARGARET <MARGARET.TOMPKINS_at_DFAS.MIL>
Date: Wed, 25 Aug 2004 14:48:50 -0500
Message-ID: <7BF02D2647E8EE4980BA85173949479E03F86634@ckc-e2-w-2.ds.dfas.mil>


Yep!

mat

Can't believe we just talked about this. Maggie

Respectfully,
> Maggie Tompkins - CAD SQA
> Corporate Applications Division
> Technology Services Organization - Kansas City
> Defense Finance and Accounting Service
> 816-926-1117 (DSN 465); Margaret.Tompkins_at_dfas.mil

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham Sent: Wednesday, August 25, 2004 12:45 AM To: oracle-l_at_freelists.org
Subject: RE: non unique Index and Primary Key on same column. Is it needed?

null values in pk columns? ain't it sorta a schema design error to have nullable columns as components of the pk?

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jacques Kilchoer Sent: Tuesday, August 24, 2004 2:15 PM
To: oracle-l_at_freelists.org
Subject: RE: non unique Index and Primary Key on same column. Is it needed?

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)=3D20
already exists, Oracle will utilize it, rather than creating a redundant =3D

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 =3D utilize
that for primary key enforcement. For example, if you created an index =3D
on
(MACHINE_KEY,USABILITY_SETTINGS_KEY), that index may also be used for = =3D
the=3D20
primary key enforcement, because the leading edge matches the key. If = =3D
the
index was on (USABILITY_SETTINGS_KEY, MACHINE_KEY) that index could not =3D
be
used to enforce a PK on MACHINE_KEY. =3D20

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_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Aug 25 2004 - 15:16:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US