Re: (Non)Unique Index Vs Unique Constraint

Jay,

Remember, both UK and PK are enforced by unique indexes. The important difference between them is a null value is allowed in UK, not in PK.

In (1), your constraint is specifically named as ct_pk1. Oracle does the for you:

1. create a unique index ct_pk for you in the default tablespace of the user.
2. use this index to create the PK.
3. makes this column not null if not already.

In (2) you are doing the step (c) yourself, but you have a very important flexibility - you have explicitly made the column not null. Oracle creates a check constraint in the format SYS_Cnnnnn. It allows you to enable or disable it at will. Then you have added the unique constraint yourself, forcing Oracle to create a unique index. So, in effect you have created a primary key constraint, but still hav some flexibility in enabling/disabling the indivisual components.

In (3) you have done the same as in (2), except that you have named the check constraint, which makes it easy in typing etc, as opposed to a name like SYS_Cnnnn; but most important in case of migrations to a different database or rebuilds, where the name is preserved. The system generated names like SYS_Cnnnn could change.

All right, now that you can do it, the question is should you define a primary key this way? The answer is an emphatic NO. A primary key is not merely to enforce unqiueness and not-null values, but provides information about the contents of the table - an extension fo the logical design. In the US, every bank account holder has a Social Security Number (or a Taxpayer's Identification Number). So you can define a UK + Not Null constraint combination on the SSN column, which may be recommended, but the PK is the account_no. Perhaps as of now, only one person is allowed to open only account in the bank, so the SSN could potentilly be the PK, but that may not hold true in the future. An account is uniquely identified by the account_no, and that column should be defined as the PK; anyone else looking at the table also gets the information.

Hope this helps.

Arup

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, January 17, 2004 10:24 PM

> Thanks a lot for your quick response Mr.Jonathan and Mr.Dennis.
>
> One more silly question...
>
> What is the difference(pros & cons) between creating PK Vs (UK+NOT NULL)
Vs
> (UK+check constraint with Not null condition)?
>
> 1)
> drop table constraint_test;
> create table constraint_test(c1 number,c2 varchar2(10));
> alter table constraint_test add constraint ct_pk1 primary key(c1);
>
> 2)
> drop table constraint_test;
> create table constraint_test(c1 number,c2 varchar2(10));
> alter table constraint_test modify(c1 number not null);
> alter table constraint_test add constraint ct_uk1 unique(c1);
>
> 3)
> drop table constraint_test;
> create table constraint_test(c1 number,c2 varchar2(10));
> alter table constraint_test add constraint ct_ck1 check(c1 is not null);
> alter table constraint_test add constraint ct_uk1 unique(c1);
>
> Thanks,
> Jay
>
>
> -----Original Message-----
> Jonathan Lewis
> Sent: Saturday, January 17, 2004 6:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Depends what you want to achieve.
>
> A non-unique index enforcing a unique
> constraint allows the constraint to be
> deferrable - so you could load some
> 'nearly unique' data against it and find
> the duplicates efficiently.
>
> However, a non-unique index requires
> one byte per entry more than the equivalent
> unique index - and some people are very
> fussy about making indexes as small as
> possible.
>
>
> As far as the optimizer is concerned, the
> unique constraint guarantees uniqueness
> of data - which allows the 'single row'
> optimisation to be used, and also results
> in an equality on the index to be costed
> at the 'unique index' cost, rather than the
> 'non unique index' cost. (But the cost thing
> changes again if the constraint is deferrable)
>
> Bottom line - if you know that you never
> need to play silly games with the constraint,
> then a unique index is more efficient, and
> helps the optimizer more than a non-unique
> index.
>
>
>
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> Next public appearance2:
> March 2004 Hotsos Symposium - Keynote
> March 2004 Charlotte NC - OUG Tutorial
> April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___February
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Saturday, January 17, 2004 3:14 AM
>
>
> > All,
> >
> > Please enlighten this Junior DBA.
> >
> > Which method is more efficient? When should I go for option (1)?
> >
> > 1)NON-UNIQUE index Vs Unique Constraint
> > drop table index_test;
> > create table index_test(c1 number,c2 varchar2(20));
> > create index i1 on index_test(c1);
> > alter table index_test add constraint index_test_uk1 UNIQUE(c1);
> >
> > 2)UNIQUE index Vs Unique Constraint
> > drop table index_test;
> > create table index_test(c1 number,c2 varchar2(20));
> > create UNIQUE index i1 on index_test(c1);
> > alter table index_test add constraint index_test_uk1 UNIQUE(c1);
> >
> > Thanks in advance,
> > Jay
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Jay
> > INET: jaysingh1_at_optonline.net
> >
> > 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jonathan Lewis
> INET: jonathan_at_jlcomp.demon.co.uk
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Jay
> INET: jaysingh1_at_optonline.net
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Arup Nanda
  INET: orarup_at_hotmail.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).