Skip to Main Content
  • Questions
  • Why PK ... why not unique index + not null

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Bala.

Asked: March 20, 2003 - 10:04 pm UTC

Last updated: October 24, 2011 - 10:40 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I just got one question.

In one of the table design, developer came up with the question,

Why should one use Primary Key and why not Unique index + NOT NULL.

It does the same thing. He is not expecting any FKs to this table in the future.


The only thing i was able to explain about the importance of PK was :


1. In the future releases of Oracle , PK is the one going to stay and we should use PK in our tables.

2. In case of replication, if the table does not have PK we cant replicate the that table.


Is there any other reason why PK is better than Unique index or Unique constraint (NOT NULL)


Thanks


and Tom said...

My question to the developer

why use a unique index + not null when you could use a primary key?

A primary key has semantic meaning, it TELLS people things.
A primary key is used by the database for query rewrites and other purposes.
A primary key in an ad-hoc query tool *MEANS* something.

Why does the develop want to hide the fact you have a primary key from the world? You should be trying to stuff as much semantic meaning, use as MANY integrity constraints in the database as you humanly can. It is not only used by the optimizer and tools -- it actually *DOCUMENTS* what is going on.


So, tell the developer to justify why they want to do it the hard way? And lose documentation? And lose meaning?

Rating

  (50 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Also one more reason

Sajid Anwar, March 21, 2003 - 10:02 am UTC

Hi Tom,
One last reason why PRIMARY KEY should be used.
Primary Key imposes UNIQUE CONSTRAINT as well. Thats not possible with UNIQUE INDEX and NOT NULL constraint.

Am i right , TOM?

Regards,
Sajid Anwar
London

Tom Kyte
March 21, 2003 - 10:19 am UTC

No, a primary key imposes a primary key constraint -- a primary key constraint says "data must be unique and not null"

A unique constraint imposes a unique constriant. That says "data must be unique"

Not convinced !!

Sajid Anwar, March 21, 2003 - 10:26 am UTC

Hi Tom,

Here is my test case:

SQL> create table mytable ( a number primary key);

Table created.

SQL> insert into mytable values(1);

1 row created.

SQL> /
insert into mytable values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (TUSKERDEV.SYS_C004635) violated


SQL> drop table mytable
  2  ;

Table dropped.

It doesnt say "PRIMARY KEY CONSTRAINT VIOLATED" rather it says UNIQUE CONSTRAINT VIOLATED. I accept your point that it has UNIQUE CONSTRAINT behaviour, that comes by default.

What do you think now?

Regards,
Sajid Anwar
London 

Tom Kyte
March 21, 2003 - 10:53 am UTC

you don't have to be convinced.

there are primary keys = unique + not null
there are unique constraints = unique.

if you want to split hairs, so be it.

ops$tkyte@ORA920> create table t ( x int );

Table created.

ops$tkyte@ORA920> create unique index t_idx on t(x);

Index created.

ops$tkyte@ORA920> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA920> /
insert into t values ( 1 )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated


Now what?  that conflicts with your statement:

....
One last reason why PRIMARY KEY should be used.
Primary Key imposes UNIQUE CONSTRAINT as well. Thats not possible with UNIQUE 
INDEX and NOT NULL constraint.
........


but it isn't a unique constraint really -- there is no constraint.  It is just the general purpose error message returned when you violate uniqueness.

 

A bit more explanation needed

Abhiit Mallick, June 01, 2004 - 7:39 am UTC

A primary key has semantic meaning, it TELLS people things.
-- What does it tells actually it contains Unique values and can not contain NULL. Other ha that anything else does it imply?
A primary key is used by the database for query rewrites and other purposes.
-- Please explain in what ther purposes the PK is used where as if we defined the column an UK+NN it will not used
A primary key in an ad-hoc query tool *MEANS* something.
-- Again please explain *MEANS* and can not a UK+NN column used for ad-hoc query

Tom Kyte
June 01, 2004 - 8:55 am UTC

it implies "i am the key, use me to select a row. I am unique, I am not null, I am immutable. I am that specific value you shall use as a foreign key value to point back to me forever"


You tell ME why you would define a primary key as a UK+NN


A primary key is a primary key. Just like your name is your name. Just like a rose is a rose. Just like a car is a car.

A unique constraint with not null is just a unique constraint with not null. It isn't a primary key.

that is all, don't get caught up in semantic games -- call things that which they are.

Good Explanation

Riaz Shahid, June 01, 2004 - 9:27 am UTC

Also a primary key can even work if there is some non-unique index on the column you are trying to "Declare" as primary key.

SQL> create table mytest as select rownum rno from user_objects where rownum<=10;

Table created.

Elapsed: 00:00:00.62
SQL> create index mytest_idx on mytest(rno);

Index created.

Elapsed: 00:00:00.41

SQL> ed
Wrote file afiedt.buf

  1* alter table mytest add constraint aa primary key(rno)
SQL> /

Table altered.

Elapsed: 00:00:00.34
SQL> analyze table mytest compute statistics for table for all indexes for all indexed columns;

Table analyzed.

Elapsed: 00:00:00.57
SQL> select * From mytest where rno=1;

       RNO
==========
         1

Elapsed: 00:00:00.03
SQL> set autotrace on 
SQL> /

       RNO
==========
         1

Elapsed: 00:00:00.97

Execution Plan
==========================================================
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=2)
   1    0   INDEX (RANGE SCAN) OF 'MYTEST_IDX' (NON-UNIQUE)




Statistics
==========================================================
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        362  bytes sent via SQL*Net to client
        425  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> insert into mytest values(1);
insert into mytest values(1)
*
ERROR at line 1:
ORA-00001: unique constraint (CR.AA) violated


Elapsed: 00:00:00.00
SQL> set autotrace off
SQL> select index_name,uniqueness from user_indexes where table_name='MYTEST';

INDEX_NAME                     UNIQUENES
============================== =========
MYTEST_IDX                     NONUNIQUE

Elapsed: 00:00:00.53 

additional points for PK

ALex V., June 01, 2004 - 10:53 am UTC


I have experience with several java and pl/sql
code generators (O/R mappers, persistance frameworks,
Oracle Designer, HeadStart). All of them read metadata
and looking for EXPLICIT primary key to generate
CRUD and other SQL.

One More Addition

Riaz Shahid, June 05, 2004 - 3:42 am UTC

Also you can't do an updateable join without a primary key, you will recieve:

ORA-01779: cannot modify a column which maps to a non key-preserved table

Tom Kyte
June 05, 2004 - 8:55 am UTC

Well, "unique" would do it technically :)

but yes, when you have a primary key, by all means say it!

ops$tkyte@ORA9IR2> create table t1 ( x int unique, y int );
 
Table created.
 
ops$tkyte@ORA9IR2> create table t2 ( x int, y int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> update ( select t1.y t1y, t2.y t2y from t1, t2 where t1.x = t2.x )
  2  set t2y = t1y;
 
0 rows updated.
 
 

Thanks for clarification

Riaz Shahid, June 09, 2004 - 8:07 am UTC


What if I have a column that have null values

Sea, June 29, 2004 - 9:46 pm UTC

I asked this question before in other forums but I have not received any reponse so far.

My problem is like this:

I need to add a new column to an existing table. This column may contain null values. After this column is added, the existing primary key of the table will not be unique unless I add this column to be part of the primary key (now consists of 9 columns). As all the primary key columns have to be NOT NULL, I am thinking of the following two ways to get around this:

(1) Add this column to be part of the primary key and set all the null value to be a dummy value.

(2) Drop the primary key and create a unique index instead of the primary key with this new column so that null values are permitted.

My questions are:

(A) If I use (2) will there be any performance issues there with null values in the unique index?

(B) Is it not recommended by having dummy values as part of the primary key?

(C) Someone mentioned above that primary key is the only one to stay in future releases of Oracle, does it mean that unique index will not be supported later?



Tom Kyte
June 29, 2004 - 10:04 pm UTC

(2) would be a bad idea. primary keys convey meaning, convey information. keep the primary key (but read on).

unique indexes are here to stay (for (c))

What you might consider, a bit more dramatic here, would be a surrogate key (single column, populated via sequence) and a UNIQUE constraint. 9 columns is getting a bit "long in the tooth" here. UNIQUE constraints permit nulls.

Will it defeat the purpose of having a Primary Key

Sea, June 29, 2004 - 10:31 pm UTC

Thanks for the quick response.

If I create a primary key which is a single column populated via sequence, would it defeat the purpose of what it is said that "primary keys convey meaning"? For this column will not be used other than just taking up space, I still need to specify the values for the 10 columns in the unique constraint in order to do updates.

Thanks!

Tom Kyte
June 29, 2004 - 10:34 pm UTC

but to join.... only one

What if no join is necessary

Sea, June 29, 2004 - 10:45 pm UTC

If there is no way of joining this table with other tables using the sequence generated primary key alone without using all or part of that 10 columns in the unique constraint, should I still create the sequence generated primary key? And why?

Thanks.

Tom Kyte
June 29, 2004 - 10:58 pm UTC

i would assume if you changed the primary key from 9 to 10, there would be chance to change it from 9 to 1.....


if not, make it a unique constraint I guess. conveys meaning, without the "NOT NULL" constraint.

Surrogate vs. semantically loaded multi-column PKs

Sergey, June 30, 2004 - 8:27 am UTC

Hi Tom,

It would be fine to hear you opinion on following thoughts.

There is ongoing arquing between supporters of surrogate, one column primary keys
and multi-column ones. The latter tend to be semantically loaded,
i.e. they contain real business data like customer SSN etc.
I'm on surrogate side and that's why:

1. Semantically loaded PKs is unstable.
They reflect business rules that change over time.
1.1. Today they say 'these 3 attributes are unique for this entity',
but six months later 'eh … with tiny exception'.
This 'tiny exception' breaks a lot of your code:
you have to add 4th column to your PK,
rewrite all ref. constraints and joins to child tables
(sometimes several levels deep),
change PL/SQL interfaces etc.
1.2. Semantically loaded PK column is usually immutable in real world
(SSN, company no etc) but there is again 'tiny' probability
that they would like to change its value –
then you have a problem.
What about typos? User entered incorrect company no,
then created some child records and then – ah – start all over again
2. They take up a space in child tables comprising sometimes more
than a half columns in a table
3. Joins become much more complex: more keystrokes,
more screen space, more likely to make an error

On the other hand surrogate keys are really immutable.
Nobody cares how do you relate your data technically –
it just must be correct and fast.
The only serious drawback that I see is when you have multi-level
parent-child hierarchy and need to answer a question
'what is the name of my grand-grand parent?'.
Then you have to join 3, 4,.. n tables instead of using simple
SELECT * FROM GRANDPARENT.

Any other drawbacks?

What in your opinion are advantages/disadvantages of surrogate keys
performance-wise in regard to

1. SELECT CHILD.* FROM CHILD, PARENT
WHERE CHILD.FK = PARENT.PK
vs
SELECT CHILD.* FROM CHILD, PARENT
WHERE CHILD.FK1 = PARENT.PK1
AND CHILD.FKn = PARENT.PKn

2. SELECT GRANDPARENT.* FROM PARENT, GRANDPARENT
WHERE PARENT.FK = GRANDPARENT.PK
AND PARENT.PK = :N
vs
SELECT * FROM GRANDPARENT
WHERE GRANDPARENT.PK1 = :N

Thanks

Tom Kyte
June 30, 2004 - 10:24 am UTC

and need to answer a question
'what is the name of my grand-grand parent?'.

that assumes name is the primary key -- to me, name would not be a primary key, names are easily duplicated. that argument only works if the primary key is what you need, which is rare.


I use surrogates most of the time. for example, my "questions" table on this site. I could have a primary key of "email, timestamp" for it is IMPOSSIBLE for a single person to ask a question in the same second or millisecond. I chose "ID" instead, populated by a sequence.

I agree with your comments -- business rules do make primary key's "changeable" sometimes -- a surrogate handles that. If my true primary key is a couple of columns, I would unique it, not null it, and use a surrogate as the primary key.

NOT NULL on a PK column

Arun Gupta, July 13, 2004 - 1:27 pm UTC

Tom,
I sometimes come across DDL like
create table t1 (c1 number(10) not null,
c2 varchar2(30) not null,
c3 date...);
alter table t1 add constraint pk_t1 primary key(c1,c2)...;

My question is from a best practices perspective, it is right to create a separate NOT NULL constraint on a PK column?

Thanks

Tom Kyte
July 13, 2004 - 1:51 pm UTC

it is somewhat redundant. not useful, not too harmful

OK

Ben, July 14, 2004 - 9:10 am UTC

Hello Tom,
Regarding using keys,Which one you prefer?Either a single
column key or multi column key?Do they have any merits
or Demerits?


Tom Kyte
July 14, 2004 - 11:40 am UTC

i use what is most appropriate.

my question and answer table on this site. a primary key could be "email_address, timestamp" -- but really the table doesn't have a natural key -- so I use a sequence.

on the other hand, if I have a lookup table of 2 character state code to state name, I'll use the natural key (2 character state code).


If I need a multi-part natural key and the natural key is never changing -- i'd use that. else I'd use a sequence as a surrogate.



IOT tables

VKOUL, July 14, 2004 - 2:49 pm UTC

You cannot substitute UNIQUE KEY + NOT NULL combination in place of PRIMARY KEY in IOT tables

A reader

A reader from Houston, June 16, 2005 - 5:19 pm UTC

You said
<Quote>
....
One last reason why PRIMARY KEY should be used.
Primary Key imposes UNIQUE CONSTRAINT as well. Thats not possible with UNIQUE INDEX and NOT NULL constraint.
........
<UnQuote>

Can you provide an example on it?

Tom Kyte
June 16, 2005 - 10:07 pm UTC

did you read my answer to that READER supplied comment???

my answer begins with "No...."

Cameron Cameron, June 20, 2005 - 10:04 am UTC


Unique Constraint + index.

A reader, August 28, 2006 - 11:39 am UTC

I have two questions.

I have seen some times unique constriants creates index and some times not.

q1.) How to decide that this unique constrint will create index or not.

q2.) Is there any field in the data-dictionary that will tell me that this index is generated due to a creation of the unique constriant on not by seperate index stmt ?

TIA


Tom Kyte
August 28, 2006 - 12:27 pm UTC

q1) if there is no existing index that can be used, it will create one.

eg: unique(a,b)

if an index exists such that A and B are on the "leading edge" of the index - such as "create index I on t(a,b,c,d,e,f)" - that index I will be used by the constraint.

If A and B are NOT on the leading edge of ANY INDEX, then the constraint will create one.

q2) not really, but a definite "clue" with a high hit ratio for correctness would be if the index_name = constraint_name. It is not 100% (i can create an index C and then add a constraint C that uses that index)

always ?

A reader, August 28, 2006 - 1:26 pm UTC

will a unique constraint "ALWAYS" create index ?

Tom Kyte
August 28, 2006 - 4:35 pm UTC

No, just right above I stated when an index will be created - when an index does not exist.

And the index does not need to be unique.


ops$tkyte%ORA10GR2> create table t ( a int, b int, c int, d int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index I_created_this_one on t(a,d);

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t add constraint a_unique unique(a);

Table altered.

ops$tkyte%ORA10GR2> alter table t add constraint b_unique unique(b);

Table altered.

ops$tkyte%ORA10GR2> alter table t add constraint c_unique unique(c) deferrable;

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select a.constraint_name, a.index_name,
  2         b.uniqueness
  3    from user_constraints a,
  4         user_indexes b
  5   where a.index_name = b.index_name
  6     and a.table_name = 'T';

CONSTRAINT_NAME                INDEX_NAME                     UNIQUENES
------------------------------ ------------------------------ ---------
A_UNIQUE                       I_CREATED_THIS_ONE             NONUNIQUE
B_UNIQUE                       B_UNIQUE                       UNIQUE
C_UNIQUE                       C_UNIQUE                       NONUNIQUE

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
I_CREATED_THIS_ONE
B_UNIQUE
C_UNIQUE


a_unique "kidnapped" the index I created on a,d
b_unique did create a unique index since there wasn't any suitable index present.
c_unique did create an index, but a non-unique one, since the uniqueness must be deferrable (allowed to be temporarily non-unique in a transaction) 

I mean to say..

A reader, August 28, 2006 - 1:27 pm UTC

is it possible to have a unique constraint without index ? just a constraint ?

Tom Kyte
August 28, 2006 - 4:36 pm UTC

no, there will be an index - whether you create it or the constraint creates it.

Help on this...

Paulo, November 08, 2006 - 9:26 am UTC

Hi Tom, i need your help on this one, to keep my primary key and my index:

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Sessão alterada.

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Sessão alterada.

SQL> DROP TABLE MYTEST CASCADE CONSTRAINTS;

Tabela suprimida.

SQL> CREATE TABLE MYTEST(STR NVARCHAR2(100));

Tabela criada.

SQL> ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST_STR PRIMARY KEY(STR);

Tabela alterada.

SQL> INSERT INTO MYTEST VALUES('A');

1 linha criada.

SQL> INSERT INTO MYTEST VALUES('a');

1 linha criada.

SQL> INSERT INTO MYTEST VALUES('B');

1 linha criada.

SQL> INSERT INTO MYTEST VALUES('b');

1 linha criada.

SQL> INSERT INTO MYTEST VALUES('C');

1 linha criada.

SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM MYTEST WHERE STR='A';

STR
--------------------------------------------------------------------------------
A
a


Execution Plan
----------------------------------------------------------
Plan hash value: 1882200678

----------------------------------------------------------------------------------

| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time
|

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |               |     1 |   102 |     2   (0)| 00:00:01
|

|*  1 |  INDEX FULL SCAN | PK_MYTEST_STR |     1 |   102 |     2   (0)| 00:00:01
|
----------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NLSSORT("STR",'nls_sort=''BINARY_CI''')=HEXTORAW('006100') )

Note
-----
   - dynamic sampling used for this statement


--------------------
Notes:
Full Scan, huum ... OK i will try another option
--------------------



SQL> SET AUTOTRACE OFF;
SQL> ALTER TABLE MYTEST DROP CONSTRAINT PK_MYTEST_STR;

Tabela alterada.

SQL> CREATE INDEX IX_MYTEST_STR ON MYTEST (NLSSORT(STR, 'NLS_SORT=BINARY_CI'));

Indice criado.

SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM MYTEST WHERE STR='A';

STR
--------------------------------------------------------------------------------
A
a


Execution Plan
----------------------------------------------------------
Plan hash value: 2122440621

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |   102 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MYTEST        |     1 |   102 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IX_MYTEST_STR |     1 |       |     1   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("STR",'nls_sort=''BINARY_CI''')=HEXTORAW('006100') )

Note
-----
   - dynamic sampling used for this statement


--------------------
Notes:
Range Scan ... Nice!! But i want to keep the primary key (because i need it) so... let's move on
--------------------


SQL> SET AUTOTRACE OFF;
SQL> ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST_STR PRIMARY KEY(STR) USING INDEX IX_MYTEST_STR;

ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST_STR PRIMARY KEY(STR) USING INDEX IX_MYTEST_STR
*
ERRO na linha 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

--------------------
Notes: 
How can i solve this problem?? Why this Error?
If i use:

SQL> CREATE INDEX IX_MYTEST_STR ON MYTEST(STR);
SQL> ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST_STR PRIMARY KEY(STR) USING INDEX IX_MYTEST_STR;

It doesn't throw any error.
---------------------------

Best Regards,

Paulo 

Tom Kyte
November 08, 2006 - 7:43 pm UTC

how about instead of scads of code, you tell us "your goal", which seems to be "unique in a case insensitive fashion"?

(that will NOT be a primary key by the way, we can make it unique, it'll not be a primary key however)

My Goal

Paulo, November 09, 2006 - 6:03 am UTC

Hi, Thanks for replying.

I want a INDEXED NVARCHAR2 Column to speed up things, and this column must have a primary key because we have foreign keys referenced to it (Example: we want 'on delete cascade', ...).
Thats all ok, but we have one problem:
Our search engine in our application needs to be case insensitive [we have migrated it from SQL Server(case insensitive) to ORACLE(case sensitive)].

So we changed the parameters NLS_SORT to BINARY_CI and NLS_COMP LINGUISTIC.

And that causes us a big problem: Queries, using that NVARCHAR2 Field, doesn't use indexes.
So we are trying to replace that index (automatically created by the primary key) with a function based index:

CREATE INDEX IX_MYTEST_STR ON MYTEST (NLSSORT(STR, 'NLS_SORT=BINARY_CI'));

This index works fine for us, but how can we keep the constraint?

Another Test:

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> DROP TABLE MYTEST CASCADE CONSTRAINTS;

Table dropped.

SQL> CREATE TABLE MYTEST(STR NVARCHAR2(100));

Table created.

SQL> CREATE INDEX IX_MYTEST_STR ON MYTEST (NLSSORT(STR, 'NLS_SORT=BINARY_CI'));

Index created.

SQL> ALTER TABLE MYTEST ADD CONSTRAINT U_MYTEST_STR UNIQUE(STR) USING INDEX IX_MYTEST_STR;
ALTER TABLE MYTEST ADD CONSTRAINT U_MYTEST_STR UNIQUE(STR) USING INDEX IX_MYTEST_STR
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

Thanks in advance,

Paulo 

Tom Kyte
November 09, 2006 - 8:39 am UTC

the index you are creating cannot replace the one you have

THEY CONTAIN ENTIRELY DIFFERENT DATA.

therefore, you need two entirely different indexes, and you have them.

think about it, what is considered UNIQUE is entirely DIFFERENT using an index on

STR

versus using an index on

NLSSORT(STR, 'NLS_SORT=BINARY_CI')




A solution!

Paulo, November 09, 2006 - 7:06 am UTC

Hi again,

I think i have found a solution:

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> DROP TABLE MYTEST CASCADE CONSTRAINTS;

Table dropped.

SQL> CREATE TABLE MYTEST(STR NVARCHAR2(100));

Table created.

SQL> ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST_STR PRIMARY KEY(STR);

Table altered.

SQL> INSERT INTO MYTEST VALUES('A');

1 row created.

SQL> INSERT INTO MYTEST VALUES('B');

1 row created.

SQL> CREATE UNIQUE INDEX IX_MYTEST_STR ON MYTEST (NLSSORT(STR, 'NLS_SORT=BINARY_CI'));

Index created.

SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM MYTEST WHERE STR='A';

STR
--------------------------------------------------------------------------------
A


Execution Plan
----------------------------------------------------------
Plan hash value: 1105013065

---------------------------------------------------------------------------------------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |               |     1 |   102 |     1   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MYTEST        |     1 |   102 |     1   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | IX_MYTEST_STR |     1 |       |     0   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(NLSSORT("STR",'nls_sort=''BINARY_CI''')=HEXTORAW('006100') )

Note
-----
   - dynamic sampling used for this statement

Now i have primary key and unique index.
And my query will use that last defined index (unique scan -> great).

The LINGUISTIC option disables unique scan if i have only a primary key.
But with this new UNIQUE INDEX: IX_MYTEST_STR my problem is solved because query uses IX_MYTEST_STR (unique scan) instead of using PK_MYTEST_STR (full scan).

------------------------------------------------------------

Test with PK_MYTEST_STR Only:

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL>
SQL> DROP TABLE MYTEST CASCADE CONSTRAINTS;

Table dropped.

SQL> CREATE TABLE MYTEST(STR NVARCHAR2(100));

Table created.

SQL>
SQL> ALTER TABLE MYTEST ADD CONSTRAINT PK_MYTEST_STR PRIMARY KEY(STR);

Table altered.

SQL>
SQL> INSERT INTO MYTEST VALUES('A');

1 row created.

SQL> INSERT INTO MYTEST VALUES('B');

1 row created.

SQL>
SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT * FROM MYTEST WHERE STR='A';

STR
--------------------------------------------------------------------------------
A


Execution Plan
----------------------------------------------------------
Plan hash value: 1882200678

----------------------------------------------------------------------------------

| Id  | Operation        | Name          | Rows  | Bytes | Cost (%CPU)| Time
|

----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |               |     1 |   102 |     2   (0)| 00:00:01
|

|*  1 |  INDEX FULL SCAN | PK_MYTEST_STR |     1 |   102 |     2   (0)| 00:00:01
|

----------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NLSSORT("STR",'nls_sort=''BINARY_CI''')=HEXTORAW('006100') )

Note
-----
   - dynamic sampling used for this statement


Best regards,

Paulo 

Non-unique vs. unique indexes

Chi H, November 14, 2006 - 9:26 pm UTC

I saw this comment:
"Unique constraints are always preferable to unique indexes."

here:
</code> http://www.psoug.org/reference/indexes.html#ixbt <code>

Can you comment on this?

I always thought the optimizer liked unique indexes because it could make the assumption of uniqueness, and benefited in certain query situations.

Tom Kyte
November 15, 2006 - 6:59 am UTC

if you have a unique constraint, a data integrity constraint that says "thou are unique" - it only makes sense to use.....

a UNIQUE CONSTRAINT


a unique index is NOT a unique constraint, it is a unique index. the optimizer will use constraints as well as available indexes and so on when deciding on what to do.

Assert all constraints - if we choose to use a unique index to enforce it, great - but don't create a unique index and tell me you have a constraint, you don't. You missed out.

Chi H, November 15, 2006 - 3:23 pm UTC

Thanks. Makes sense.

Chi H, November 16, 2006 - 7:01 pm UTC

Back when I first started using Oracle, I was told unique scans are better than range scans, which lead to my previous question regarding unique index vs. unique constraint.

Here's what I mean:
SQL> create table t1 as select * from user_objects;

Table created.

Elapsed: 00:00:00.16
SQL> alter table t1 add constraint t1_pk primary key (object_id);

Table altered.

Elapsed: 00:00:00.08
SQL> create index t1_u1 on t1 (object_name);

Index created.

Elapsed: 00:00:00.05
SQL> alter table t1 add constraint t1_uk unique (object_name);

Table altered.

Elapsed: 00:00:00.02
SQL> set autotrace traceonly
SQL> select *    
  2  from t1
  3  where object_id = 1;
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   2    1     INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        230  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


SQL> select *
  2  from t1
  3  where object_name = 'XYZ';
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   2    1     INDEX (RANGE SCAN) OF 'T1_U1' (NON-UNIQUE)




Statistics
----------------------------------------------------------
        185  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        230  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> alter table t1 drop constraint t1_uk;

Table altered.

Elapsed: 00:00:00.01
SQL> drop index t1_u1;

Index dropped.

Elapsed: 00:00:00.04
SQL> create unique index t1_u1 on t1 (object_name);

Index created.

Elapsed: 00:00:00.02
SQL>  select *
  2   from t1
  3   where object_name = 'XYZ';
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T1'
   2    1     INDEX (UNIQUE SCAN) OF 'T1_U1' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        230  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Just want to hear your thoughts on this.

Thanks. 

Tom Kyte
November 17, 2006 - 3:17 am UTC

use a unique constraint when you have something unique.

period

end of discussion

that is the way to do it.


a unique constraint will by default create a UNIQUE index if no existing index can be used.


and a "unique scan" is no better, faster, cheaper, whatever - than a "range scan that returns zero or one rows"

It doesn't even make SENSE to compare a unique scan to a range scan really.

function-based unique constraint !!

sunj, September 21, 2007 - 8:27 am UTC

I was going to create a function-based unique index, which will ensure that my column value will be unique going forward. But my existing data is not unique. How can I do the equivalent of UNIQUE CONSTRAINT with NOVALIDATE using a function-based index.

CREATE TABLE users (
userid INTEGER NOT NULL,
username VARCHAR2(60));

ALTER TABLE users ADD
CONSTRAINT users_pk
PRIMARY KEY (userid)
ENABLE
VALIDATE;

CREATE UNIQUE INDEX users_ix1 ON users
(UPPER("USERNAME"));

This is not valid, but I want to be able to achieve similar functionality:
ALTER TABLE users
ADD UNIQUE (UPPER("username"))
ENABLE NOVALIDATE;

I know I can use better functions than "upper", but my question specifically is - "How can I implement a NOVALIDATE on a case-insensitive UNIQUE constraint?"

I'd appreciate your suggestins.
Thanks
Tom Kyte
September 26, 2007 - 7:58 am UTC

I would say this is a useless requirement. rethink your approach. It does not make sense to me to have this sort of situation.

there is no such thing as "novalidate unique indexes", think about what would happen in the future if you needed to rebuild or recreate this index (how to discern between what was "there" and what wasn't)

if you have some field that you can say "if this is greater than some value, then uniquely index it", you can achieve this in a way:

ops$tkyte%ORA10GR2> create table t as select * from all_users where rownum <= 5;

Table created.

ops$tkyte%ORA10GR2> insert into t select * from t;

5 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create unique index t_idx on t
  2  ( case when created >= to_date( '26-sep-2007', 'dd-mon-yyyy' ) then username end );

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t(username,user_id,created) values ('xxx', 1000, sysdate );

1 row created.

ops$tkyte%ORA10GR2> insert into t(username,user_id,created) values ('xxx', 1000, sysdate );
insert into t(username,user_id,created) values ('xxx', 1000, sysdate )
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.T_IDX) violated


ops$tkyte%ORA10GR2> select username, count(*) from t group by username;

USERNAME                         COUNT(*)
------------------------------ ----------
FB_DEMO                                 2
BIG_TABLE                               2
A                                       2
BIGGER_TABLE                            2
DEMO_23                                 2
xxx                                     1

6 rows selected.

ops$tkyte%ORA10GR2> insert into t(username,user_id,created) values ('A', 1000, sysdate );

1 row created.

ops$tkyte%ORA10GR2> select username, count(*) from t group by username;

USERNAME                         COUNT(*)
------------------------------ ----------
FB_DEMO                                 2
BIG_TABLE                               2
A                                       3
BIGGER_TABLE                            2
DEMO_23                                 2
xxx                                     1

6 rows selected.


but not how A is unique going forward ONLY, not retroactively

The use of Unique+ Not Null instead of Primary Key

Sharad Kumar Singh, December 03, 2007 - 12:50 pm UTC

I have a situation something like follows. I have a table like this :
po_no varchar2(5)
po_dt date
cat_no varchar2(11)
po_sl_no number(2)
l_sl_no number(4)
po_desc varchar2(50)

primary key on po_no, po_dt, cat_no, po_sl_no, l_sl_no.

The above table contains the description of a purchase order in multiple lines where each line is 50 characters. The sequence of these lines are maintained in the l_sl_no column. This is accessed through oracle forms application. The user sometimes wants to edit the description and wants to insert a new line in between (say for eg. a line after line no 3). I was using execute_query to get the data from this table and INSERT_RECORD, and before committing i re-sequenced the l_sl_no but on saving i get primary key violation if I have primary key on po_no, po_dt, cat_no, po_sl_no, l_sl_no. But if i use a UNIQUE+NOT NULL contraint everything works fine. Why is this so if both means the same.
Sharad Kumar Singh
Tom Kyte
December 03, 2007 - 7:47 am UTC

you'd need an example to demonstrate with (NOT FROM FORMS, just something in sqlplus) because what you say "does not compute"

primary key v/s indexes

Raghu, December 03, 2007 - 2:20 pm UTC

Primary keys need to have not null columns.
I am sure there is a reason for that but can't construct it, Can you refresh my basics? Why don't we allow nullable columns to be part of primary key?

A reader, December 05, 2007 - 8:31 am UTC

"Primary keys need to have not null columns.
I am sure there is a reason for that but can't construct it, Can you refresh my basics? Why don't
we allow nullable columns to be part of primary key?"

Pretty straight forward...does null=null? The problem is that you really don't know.

unique constraint coupled to index?

Don, July 14, 2008 - 7:40 pm UTC

Tom,

A few posts back in this thread the following question was asked:
"Is there any field in the data-dictionary that will tell me that this index is generated due
to a creation of the unique constriant on not by seperate index stmt ?"

You replied that there's not a 100% reliable method. I find this surprising because Oracle somehow knows if the index and constraint are coupled. If they're coupled both are dropped when the constraint is dropped. If they're not coupled, only the constraint is dropped and the index is left in place.

Given this, and with the risk of being redundant I'll restate the question: Is there some obscure query that would return this information?

I'm interested in this because 'exp/imp' and 'expdp/impdp' decouple previously coupled constraints/indexes. This causes one of our schema migration scripts to fail when it attempts to move an index to a new tablespace. The script is written to assume that a 'drop constraint' will drop the index with it, because the author knows that they were originally coupled. The script does the `drop constraint¿, and then attempts to recreate the constraint with the index in the new tablespace using an 'alter table ... add unique constraint ... using index...' statement. That statement fails because the index already exists. The schema migration tool the project team chose (Liquibase) is very inflexible and won't allow them to trap the error, so the entire script fails!

I'd like to write a utility to re-couple any decoupled constraints/indexes. They could run it just before the migration script or after and imp.

Thanks much for your valuable insight.

Don

Tom Kyte
July 15, 2008 - 9:42 am UTC

in current relesases, user_constraints has this information - you can just query it out now


ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(x);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select index_name from user_constraints where constraint_name = 'T_PK';

INDEX_NAME
------------------------------
T_IDX


unique constraint coupled to index?

Don Kaskowitz, July 15, 2008 - 10:09 am UTC

In 10.1 EE USER_CONSTRAINTS.INDEX_NAME doesn't tell me if they're coupled, i.e., if dropping the constraint will take the index with it:

SQL> create table t ( x int, y int );

Table created.

SQL> create index t_idx on t(x);

Index created.

SQL> alter table t add constraint t_pk primary key(x);

Table altered.

SQL> select index_name from user_constraints where constraint_name = 'T_PK';

INDEX_NAME
------------------------------
T_IDX

SQL> alter table t drop constraint t_pk;

Table altered.

SQL> select index_name from user_indexes where table_name = 'T';

INDEX_NAME
------------------------------
T_IDX

SQL> drop index t_idx;

Index dropped.

SQL> alter table t add constraint t_pk primary key(x);

Table altered.

SQL> select index_name from user_constraints where constraint_name = 'T_PK';

INDEX_NAME
------------------------------
T_PK

SQL> alter table t drop constraint t_pk;

Table altered.

SQL> select index_name from user_indexes where table_name = 'T';

no rows selected

Tom Kyte
July 15, 2008 - 8:02 pm UTC

but they are coupled. Dropping a constraint does not assure the corresponding index will be dropped. That was changed a while ago.

I think your code would need to drop the constraint and the index if that is what you desire.


you cannot drop the index and have the constraint (they are coupled) but it is perfectly OK to drop the constraint and leave the index (desirable in many cases, in fact drop constraint has a keep index clause even...)


I cannot reproduce your findings however:

ops$tkyte%ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(x);

Index created.

ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> drop index t_idx;
drop index t_idx
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key


ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select index_name from user_constraints where constraint_name = 'T_PK';

INDEX_NAME
------------------------------
T_IDX

ops$tkyte%ORA10GR2> alter table t drop constraint t_pk;

Table altered.

ops$tkyte%ORA10GR2> select index_name from user_constraints where constraint_name = 'T_PK';

no rows selected

PK

A reader, July 15, 2008 - 5:10 pm UTC


unique constraint coupled to index?

Don, July 16, 2008 - 10:58 am UTC

My DDL differs from yours in that I'm looking at USER_INDEXES for the index and you're looking at USER_CONSTRAINTS for it.

Mine:
select index_name from user_indexes where table_name = 'T';

Yours:
select index_name from user_constraints where constraint_name = 'T_PK';

I understand that it's normal and expected for the index to be left in place when the constraint is dropped, if it was created independently from the constraint, and for it to be dropped if it is created implicitly with the constraint.

What I don't understand is how Oracle can know when they're coupled, but I can't can't tell if they're coupled or not using a dictionary query.

On one schema the index and constraint are coupled, on another copy of the same schema they may not be coupled(because exp/imp decoupled them). So, as far as our schema migration script is concerned, we're stuck because according to the developers they can't put error handling OR conditional processing in it.

Tom Kyte
July 17, 2008 - 11:19 am UTC

... we're stuck because according to the
developers they can't put error handling OR conditional processing in it.
....

doesn't that strike anyone as "just wrong"?


I messed up with my query above didn't I :)

in any case - if you want the index to "go away", use drop index, if you want it to "stay", use keep index - you can control during the execution of the drop constraint - what happens

ops$tkyte%ORA10GR2> select index_name from user_constraints where constraint_name = 'T_PK';

INDEX_NAME
------------------------------
T_IDX

ops$tkyte%ORA10GR2> alter table t drop constraint t_pk drop index;

Table altered.

ops$tkyte%ORA10GR2> select index_name from user_indexes where index_name = 'T_IDX';

no rows selected


unique constraint coupled to index?

Don, July 17, 2008 - 1:18 pm UTC

That'll do it!

We'll include 'drop index' in our drop constraint statements. That'll drop the index regardless of whether it's coupled or not. No error handling or conditional processing needed (I agree it's "just wrong").

I won't be offended if you suggest that in the future I read the manual first.

Thanks much!

Oracle e-Business data model

Matthew McPeak, July 20, 2008 - 11:59 pm UTC

Tom,

I was reading, with interest, your discussion about constraints vs indexes. I understand and agree with all your points. But I've often wondered: why does Oracle not use PK/FK constraints in its e-Business data model? Last time I checked for them (which I'll admit was a few years ago, now), they hardly had any.

Is it just a throw-back to a questionable design decision made years and years ago? Some point to the Oracle eBS data model as some sort of reference data model -- a reference of how things "should" be done -- since it was delivered by Oracle itself. There is the thought that maybe Oracle knows something that we don't.

Any comments?

Thanks,
Matt

Tom Kyte
July 22, 2008 - 10:15 am UTC

much of that code originated in version 6.

primary keys, foreign keys, constraints were "supported" syntactically

but they were not enforced

you are seeing a lot of legacy there.


Also, a 3rd party product like that is typically only 50-80% "finished". When you install, you spend the first large chunk of time "finishing" the code - which involves in many cases you implementing your idea of what the constraints should be.

When we write our own applications - we know what the business rules are. SAP, Peoplesoft, Oracle apps, Siebel - they do not always know that when they ship the code.

NN UK -> PK

Duke Ganote, March 30, 2009 - 2:44 pm UTC

Suppose I inherited a large table where someone decided that a not-null unique constraint was "better" than a primary key. And this table is used by several tables for referential integrity.

Can I change the constraint from unique to primary key without dropping and re-building the integrity constraints?

I'm using 10gR2.
Tom Kyte
March 30, 2009 - 5:53 pm UTC

Ok, this error message made me laugh. It sounds like I wrote the 'cause' portion:

ops$tkyte%ORA10GR2> alter table p add constraint p_pk primary key(x)
  2  /
alter table p add constraint p_pk primary key(x)
                                  *
ERROR at line 1:
ORA-02261: such unique or primary key already exists in the table


ops$tkyte%ORA10GR2> !oerr ora 2261
02261, 00000, "such unique or primary key already exists in the table"
// *Cause: Self-evident.
// *Action: Remove the extra key.



you cannot have both at the same time. Even if you could, the child tables are hardwired to the existing constraint - they'd have to drop and recreate too.

You'll probably just want to live with it.

yep

Duke Ganote, March 30, 2009 - 6:01 pm UTC

That's pretty much what I guessed. I decided to rename it to indicate that it is the intended PK until, in my "copious spare time", I want deal with dropping constraints, re-validating, etc etc

To get more knowledge

shekhar p. sinha, July 24, 2009 - 5:16 am UTC

If I define 3 cols as unique+not null then any 1 of them can be used as pk? But, I hv found that all the 3 cols same as pk b'coz all 3 pk cols recognises all 3 fk as child
and vice versa. pls help
Tom Kyte
July 26, 2009 - 7:20 am UTC

if you define 3 attributes as unique and not null, then THEY (all three) represent your unique key.

any one of them by themselves - not unique.


I have no idea

a) what you are saying
b) what you are asking

really.


shekhar p. sinha, July 24, 2009 - 5:25 am UTC

If I define 3 cols as unique+not null then any 1 of them can be used as pk? But, I hv found that
all the 3 cols same as pk b'coz all 3 pk cols recognises all 3 fk as child
and all the 3 fk cols recognises all 3 pk as parent. pls help


Will I be wrong to say?

shekhar p. sinha, July 24, 2009 - 5:37 am UTC

Subject: " Invention of New RDBMS Rule by Shekhar P. Sinha "


Dear Sir,

I have a pleasure to mail to you.

I have found that " In RDBMS-Oracle only one primary key can be

defined in a single table" but " I have found that in RDBMS-Oracle

more than one primary key can be defined ".

To my surprise in all the books of whether Codd's Rdbms or

RDBMS-Oracle it is written that only a single primary key can be

defined and the software of Oracle also follows the same method

of single primary key.

So, in this regard I think there should be " Change of Codd's

RDBMS Rule " since " I have invented that more than one primary

key can be defined in RDBMS-Oracle single table"

I have been trying hard to put this " Research in the world of

RDBMS " for that reason I need your support and help.

I hope that I will get response fromyour kind end.

My research was very very successfully lab tested done on 4th July,saturday,2009.


Thanking you,

with regards,

Shekhar P. Sinha, Guwahati, Assam, India.

email id : shekhar_shk@rediffmail.com

mobile no : +91-0361-98640-63046
Tom Kyte
July 26, 2009 - 7:23 am UTC

No, you have not.

there is one primary key.

You have other attributes that are:

a) unique when considered together
b) not null

but they are not a primary key. No idea why you think you have 'invented' or even discovered anything?? (anything that is not already clearly documented that is)

A reader, October 30, 2009 - 3:01 pm UTC

Hello Tom,


I find this page interesting and have similar question that "why NOT NULL + UNIQUE INDEX instead of PRIMARY KEY?"


I am working in a datawarehouse where data modeler have define in every tables (i.e. dev., qa., prod. database)

NOT NULL + UNIQUE index


I don't undestand there idea behind using NN + UNIQUE index instead of primary key, when arguing with the data modeler
he say they have standard that they follow since many years and ignore my comment



I have below two question
===========================


Could you please kindly explain what are the benefit of defining NN + Unique index compare to primary key ?

Also do you think any maintenance overhead if primary key is use and business want to add or remove the key in the future ?


Thanks


Tom Kyte
November 09, 2009 - 11:33 am UTC

... he say they have standard that they follow since many years and ignore my
comment ...

so, just turn to the person and say this as loud as you can

"so you meant that in the 21st century, you are still doing things they way they did it in the 20th century? Are you telling me nothing has changed in 15 years? Isn't it true that 15 years ago a 100gb Oracle database was considered large, in 1997 they thought a terabyte was frightenly large (so much so, they put out press releases telling us you could actually do it) - but today databases of 10's to 100's of terabytes are common, normal - do you not think that things might have changed over time. And, just because you did something in the past does NOT mean it was right back then either"


... Could you please kindly explain what are the benefit of defining NN + Unique
index compare to primary key ? ...

there are none. They can do that, but only as long as they also add the primary key constraint as well (we'll use that index and not create another one)

... Also do you think any maintenance overhead if primary key is use and business
want to add or remove the key in the future ?
...

you would never remove a key, you might CHANGE it, but that is entirely different from removing it.

And the work would be the same.

A reader, December 21, 2009 - 12:09 pm UTC


two different answers

A reader, May 08, 2010 - 2:07 pm UTC

Hi Tom,

You have given two different answers for redundant null constraints. I am finding it difficult to conclude the reason being a novice oracle developer. Please shed some light on this with an example.

Question 1

Is it a bad idea to place constraints (pk and unique)
on non unique indexes .Should we use this freely ?

Followup October 3, 2005 - 11am Central time zone:

It is a bad idea to ask the database to use a non-unique index to enforce a unique or primary key?

In general - no, it is not a bad idea, if you have deferrable constraints, it would be mandatory in
fact.

It *may* have a subtle, small, tiny impact on the optimizer (no index unique scan - always the
chance of duplicates in there).

I would not (I do not) make all constraints deferrable however (especially primary keys - if you do
that, make sure to add a redundant NOT NULL constraint!! if the primary key is deferrable - so is
the NOT NULL part and the index can be used in less circumstances)

Do it when you have a good reason to do so.


Question 2:


Tom,
I sometimes come across DDL like
create table t1 (c1 number(10) not null,
c2 varchar2(30) not null,
c3 date...);
alter table t1 add constraint pk_t1 primary key(c1,c2)...;

My question is from a best practices perspective, it is right to create a separate NOT NULL
constraint on a PK column?

Thanks


Followup July 13, 2004 - 1pm Central time zone:

it is somewhat redundant. not useful, not too harmful





My Queries are as follows

1) Are there any performance issues with the redundant not null constraints ?

2) When to use redundant not null constraints?

3) Whether it's a good idea to keep the redundant not null constraints on a table?

4) Can you please provide scripts to identify the duplicate not null constraints?


Thanks for providing such a excellent service to oracle community.




Tom Kyte
May 10, 2010 - 6:31 pm UTC

where did you get question 1 from - I think it is out of context.

There, the point was:

"hey, we have a non-unique on something. We would like to put a primary key/unique constraint on that same thing. Is that a bad idea"

And my answer was: "it is ok to do that"

And the implied thought was "Thank goodness you are using constraints - keep on going"


They were NOT asking:

should I create a non-unique index
OR
should I create a primary key/unique constraint.

They were asking:

is it ok to have a non-unique index enforce a primary key/unique constraint.




My answer is and has been and will be consistent and never ending. IF YOU HAVE A UNIQUE, PRIMARY KEY, or in short ANY SORT OF CONSTRAINT (null, check, whatever) the only - repeat the ONLY way to do it is - via a constraint.

Do not put a unique index + not null check on a column and call it a primary key, it isn't.

DO put a primary key constraint on a column, then you may call it a primary key



Do not confuse a discussion about how Oracle enforces a constraint (we can use a unique or non-unique index - either one) with whether a constraint should or should not exist!





I see nothing conflicting (in neither q1 nor q2 did I say "use a primary key AND a not null").



Put a constraint in place once.

Realize that if you are a primary key, you are already not null - putting not null on a primary key makes you look like you do not understand or know what you are doing. Doesn't matter if it doesn't or does affect performance, it would not be 'right' so don't do it.

excellent

A reader, May 10, 2010 - 8:25 pm UTC

Your answers are excellent as usual. Thanks for your patience in explaining the basic concepts.

Difference between pedantic and appropriately

Dardo Rojo, September 02, 2010 - 10:54 am UTC

Many contributors should understand that there is a huge difference between being pedantic and being appropriately right.

Natural primary key exists... why cannot we set it as a primary key?

wakula, October 06, 2011 - 8:53 am UTC

Hi Tom,

I have tables where single unique row is identified by a combination of "key" columns. Looks like a natural primary key?
The columns are of type "text", or rather "varchar2".
Tricky part: The text can be empty. And Oracle treats empty string as NULL.

Example:
create table AVG_PRICE(CLASS varchar2(128),PRODUCT_NAME varchar2(128),PRICE number);
insert into AVG_PRICE values ('Vegetable','Tomato',15);
insert into AVG_PRICE values ('Vegetable','Pomato',10);
insert into AVG_PRICE values ('Vegetable','',12.5);
insert into AVG_PRICE values ('Fruit','Apple',25);
insert into AVG_PRICE values ('Fruit','Orange',24);
insert into AVG_PRICE values ('Fruit','Lemon',20);
insert into AVG_PRICE values ('Fruit','',23);

To have a primary key I would need an additional column with ID. Or maybe ROWID?
What's the justification to enforce PRIMARY KEY in that situation? "Why should one use Primary Key and why not" set of unique values?
Shouldn't PRIMARY KEY be rather defined as "unique + no more than 1 null"? Or maybe NULL should be differ from empty string?

Regards
Tom Kyte
October 06, 2011 - 9:18 am UTC

that is not a tricky part, it is a fact. And another fact is that none of the columns in a primary key can be null - so therefore, these attributes cannot be the primary key.


do not use rowid since rowids are no longer immutable - they may change over time - they are only useful for very short durations.


Always interesting to read

Galen Boyer, October 07, 2011 - 2:02 pm UTC

Hi Tom,

I use surrogates almost all the time, and then I demand that some other group of columns be defined as an AK (sometimes, this doesn't always happen, but ...), so we are probably in agreement.

Now reading Shekhar p. Sinha's posting, I was laughing because he hasn't discovered anything, but he does actually touch on something which is that the Primary Key is many times arbitrary. There are many times a table has multiple keys to it but we choose one as a PK. That choice is arbitrary. To me, the PK could easily have been just "Key" and both choices could have been identified as the "Key". I'm not suggesting Oracle drop the PK. Its clearly the way to do it in Oracle, I'm just suggesting that the concept of a PK is no more theoretically correct than a KEY.
Tom Kyte
October 07, 2011 - 2:17 pm UTC

AK?

alternate?


A primary key is a set of non-nullable attributes that are immutable. That is different from a "key", a "key" can point to many rows. A primary key points to one and exactly one row - always the same row - forever.

A PK is just a key

Galen Boyer, October 07, 2011 - 2:37 pm UTC

The point is the term Primary. There are times when the choice of a primary key is arbitrary.
Tom Kyte
October 07, 2011 - 3:01 pm UTC

It is not arbitrary, there might be times when you have more than one choice for the primary key (because someone FORCED a surrogate on you for example when you had a perfectly good natural key) - but it is not arbitrary, it is something you chose when implementing your model.


Give me an example otherwise.

Followup: Natural primary key exists... why cannot we set it as a primary key?

wakula, October 24, 2011 - 10:35 am UTC

Tom,
Thanks for your answer.
You've said that is not a tricky part, it is a fact
Empty string is not the same as "undefined value".
What solution would you suggest to distinguish NULL and empty string?
Would you give arguments to define an artificial primary key (ex. number from a sequence) or to leave the table without any PK?

P.S.
We're most likely to use an unique value (ex. CHR(0)) instead of empty string/NULL. Drawback: Value of CHR(0) become disallowed/reserved.
Tom Kyte
October 24, 2011 - 10:40 am UTC

In Oracle the empty string is NULL, there is no way around that. There is no way to distinguish between them since the concept of an empty string just does not exist.

I would not use chr(0), that'll play havoc with various character set translations.


Enforcing not NULL before creating a PK

Mitchell, February 12, 2013 - 9:16 am UTC

Hi all,

I've found a situation and thought I'd share it while it's still fresh in my mind. I'm redefining a relatively large (for the box) partitioned table using dbms_redefinition in a (10gR2) data warehouse for the purpose of compression. There are no NOT NULL constraints on the fields destined to join the PK. The script was generated by OMS as a "reorganise" option. Initial steps are: create the new table, start loading data in, create a UNIQUE index on the PK fields and then assert the PK constraint. It's taking FOREVER to create the PK constraint (around an order of magnitude longer than it takes to full table scan with one thread and create the new index). Upon watching the session trace, I can see it's doing a full scan on the new table multiple times.

Why redefine? Well, it's partitioned and has bitmap indexes we always seem to be using, none of them on compressed data, and it won't let you compress any partitions unless there's already one or more.

But to cut a long story a little less long, it seems to be doing a full scan on the table for each field in turn to check for NULLs...

If the fields were set to NOT NULL on creation, I think this would be quicker as it will avoid the NOT NULL validation. By all means, please correct the assumption if I'm wrong. It may also be that the older OMS was simply developed to work in a way that's compatible with 8i through 11g, and it could have created the first index as a PK. Food for thought with respect to older databases perhaps.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.