From oracle-l-bounce@freelists.org Wed Aug 25 00:42:01 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i7P5g1j07311 for ; Wed, 25 Aug 2004 00:42:01 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i7P5g1I07306 for ; Wed, 25 Aug 2004 00:42:01 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C22A972C9D3; Wed, 25 Aug 2004 00:44:43 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16629-84; Wed, 25 Aug 2004 00:44:43 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 18B5E72C404; Wed, 25 Aug 2004 00:44:43 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 25 Aug 2004 00:43:19 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 70C7472C106 for ; Wed, 25 Aug 2004 00:43:18 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 15527-82 for ; Wed, 25 Aug 2004 00:43:18 -0500 (EST) Received: from IPOfCard1.guest-tek.com (unknown [209.172.111.2]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id EA1C772C0C5 for ; Wed, 25 Aug 2004 00:43:17 -0500 (EST) Received: from MWF600XL ([172.17.3.222]) by IPOfCard1.guest-tek.com (8.11.6/8.11.6) with SMTP id i7P5TYI14937 for ; Tue, 24 Aug 2004 23:29:34 -0600 From: "Mark W. Farnham" To: Subject: RE: non unique Index and Primary Key on same column. Is it needed? Date: Wed, 25 Aug 2004 01:44:57 -0400 Message-ID: MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.6604 (9.0.2911.0) In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Importance: Normal X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 8274 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Jacques Kilchoer Sent: Tuesday, August 24, 2004 2:15 PM To: oracle-l@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)=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@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@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 -----------------------------------------------------------------