primary key constraint vs non-unique index+PK", version 8.1.7.4
Joseph Fabiano, March 20, 2003 - 10:26 pm UTC
Tom,
Can you explain with an example the feature that this
person is describing? I'm interested in knowing how
to disable the PK, keep the index, perform a direct sqlload, then place the PK
back without recreating the index. I was able to
disable the PK and keep the index but the index was still
unique.
March 21, 2003 - 8:29 am UTC
just
alter table t add constraint t_pk primary key (a,b) DEFERRABLE;
you'll note at that point, the index generated is non-unique.
or,
create index t_idx on t(a,b);
alter table t add constraint t_pk primary key(a,b);
that way -- you'll have a non-deferrable constraint that uses a non-unique index to enforce the primary key.
for a partitioned table
A reader, March 21, 2003 - 1:23 pm UTC
Hi
For a partitioned table I think your only bet is create the non-unique index first then add the PK
alter table t add constraint t_pk primary key (a,b) DEFERRABLE;
doesnt work for a partitioned table does it?
March 21, 2003 - 2:19 pm UTC
ps$tkyte@ORA920> CREATE TABLE t
2 (
3 x int ,
4 collection_year int,
5 y int
6 )
7 PARTITION BY RANGE (COLLECTION_YEAR) (
8 PARTITION PART_95 VALUES LESS THAN (1996) storage (initial 1m),
9 PARTITION PART_96 VALUES LESS THAN (1997) storage (initial 5m),
10 PARTITION PART_01 VALUES LESS THAN (MAXVALUE) storage (initial 1k)
11 )
12 ;
Table created.
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable;
Table altered.
yes, it does work.
yea well but I was referring partitioned PK
A reader, March 21, 2003 - 2:55 pm UTC
Hi
I was referring partitioned index, with your way it is not a partitioned index doh
March 21, 2003 - 3:06 pm UTC
so sorry, crystal ball is on the blink again. Everything is being intrepreted literraly in the meanwhile ;)
unless you are partitioning by the primary key -- no go in EITHER case
ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable
4 using index local;
alter table t
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
<b>cannot create the locally partitioned index non-unique index using the all primary key...</b>
ops$tkyte@ORA920>
ops$tkyte@ORA920> create index t_pk on t(x) local;
Index created.
ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable ;
alter table t
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
<b>and if it is there , it fails</b>
ops$tkyte@ORA920> drop index t_pk;
Index dropped.
ops$tkyte@ORA920> create index t_idx on t(x) local;
Index created.
ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable ;
alter table t
*
ERROR at line 1:
ORA-01408: such column list already indexed
<b>as you can see -- it is trying to create a global non-unique index</b>
the index in this case would have to be a global, non-unique index partitioned by the PRIMARY KEY values itself. and that can in fact be done in the constraint itself:
ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable
4 using index
5 global
6 partition by range(x)
7 ( partition p1 values less than (1000),
8 partition p2 values less than (maxvalue) );
Table altered.
is this a typo?
A reader, March 21, 2003 - 7:12 pm UTC
Hi
from one of your post you mentioned this:
--------------------------------------------------------
alter table t add constraint t_pk primary key (a,b) DEFERRABLE;
you'll note at that point, the index generated is non-unique.
--------------------------------------------------------
In your last post however you said this:
--------------------------------------------------------
ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable ;
alter table t
*
ERROR at line 1:
ORA-01408: such column list already indexed
as you can see -- it is trying to create a unique index
--------------------------------------------------------
is DEFERRABLE trying to create a non-unique index or an unique index...?
March 21, 2003 - 7:23 pm UTC
corrected to read as:
as you can see -- it is trying to create a global non-unique index
why partition column must be part of unique ndex?
A reader, March 22, 2003 - 11:55 am UTC
hi
ops$tkyte@ORA920> alter table t
2 add constraint t_pk
3 primary key(x) deferrable
4 using index local;
alter table t
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index
Why we cannot create unique index partitioned locally without involving the partition column?
Also I have a question regarding unique values in partitions, if I have three partitions partitioned by date and my primary key is customer_id, can customer_id repeat in different partitions?
March 22, 2003 - 12:41 pm UTC
because it would obviate all of the usefulness of partitioning.
In order to insert a row -- we would have to lock ALL index partitions to prevent that same value from being inserted in there, there goes availability, there goes scalability, there goes concurrency -- right out the window.
If you hash partitioned into 10 partions
And you had a local index on (X)
And X was not the partition key (so X=5 could go into ANY partition)
And X had to be unique -- the only way to do that would be to lock the 9 index partitions you are NOT inserting into (to prevent X=5 from going in there, goodbye concurrency -- AFTER checking if X=5 was already in there, goodbye availability -- every partition MUST be available ALL of the time)....
The answer to the last question should be self evident.
IF your primary key for the table is customer id
THEN customer id in the table is unique.
I dont understand last explanation
A reader, March 22, 2003 - 1:35 pm UTC
Hi
I dont understand the last explanation at all :-(
Why we have to lock all partitions to insert a row?
If I have a table
empno hiredate
----- -----------
1 200110
2 200111
3 200112
hiredate is partition key by year-month
if let´s say we can create an partitioned local unique index on empno (ok its not possible but to simulate the locking issue...)
I then do
insert into table values (4, 200112);
and the other session just does the same in sert a few seconds after me what happens..?
Why this would lock the ther three partitions (200210, 11 and 12)?
if I do
insert into table values (5, '');
then this would go to the partitition with maxvalue, would this behaves as previous insert statement?
March 23, 2003 - 4:59 pm UTC
say session 1 does:
insert ( 4, 200112 );
and session 2 does
insert ( 4, 200111 )
and session 3 does
insert ( 4, 200110 )
Now, tell me what happens.... All of these go on at the same time bear in mind. Unless you locked the partions pertaining to 200111 and 200110 when you modified the index associated with partition 200112 -- what would prevent sessions 2 and 3 from creating another empno = 4?
And further, if session 1 did not scan these indexes -- how would session 1 know that empno = 4 doesn't already exist?
Maybe i fail to understand...
VJ, April 23, 2003 - 8:22 pm UTC
Hi,
I want to create a partitioned table. I want to maintain a primary key to the table. One part of the table partition i would like to truncate and load data on a daily basis. I am having difficult time achieving this.
---
I want partitioned table and i want primary key, so i wont insert duplicate records...
---
Kindly help....
Thanks a million
VJ
The steps i followed are here below:
-------
1 create table t7 (
2 id number,
3 name varchar2(30),
4 status varchar2(30),
5 pkey number)
6 partition by range (pkey)
7 (partition p1 values less than (3),
8* partition p2 values less than (6))
SQL> /
Table created.
Elapsed: 00:00:00.78
SQL> alter table t7 add constraint pk_t7 primary key (id,name,status);
Table altered.
Elapsed: 00:00:00.41
SQL> begin
2 for i in 1..10 loop
3 insert into t7 values (i,'vj','A',1);
4 insert into t7 values (i,'dto','A',2);
5 insert into t7 values (i,'vld','N',4);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
SQL> select count(0) from t7 partition (p1);
COUNT(0)
----------
20
Elapsed: 00:00:00.08
SQL> select count(0) from t7 partition (p2);
COUNT(0)
----------
10
Elapsed: 00:00:00.01
SQL> alter table t7 truncate partition p1;
Table truncated.
Elapsed: 00:00:00.17
SQL> begin
2 for i in 1..10 loop
3 insert into t7 values (i,'vj','A',1);
4 insert into t7 values (i,'dto','A',2);
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01502: index 'CDW_MKT.PK_T7' or partition of such index is in unusable
state
ORA-06512: at line 3
Elapsed: 00:00:00.02
April 23, 2003 - 9:11 pm UTC
search for LOCAL on this page and use a LOCALly partitioned index so the truncate doesn't invalidate the default GLOBAL index that is being used.
Maybe i fail to understand...
VJ, April 23, 2003 - 10:29 pm UTC
Tom,
I feel a quick example would do more good listing my requirements.
1) Paritiioned table
2) Maintaining Primary Key/Unique
Thanks a lot in advance
VJ
April 24, 2003 - 7:34 am UTC
I already HAVE an example, please -- READ this page. goto the top, search for LOCAL (like i said).
READ this page (it has an example of exactly what you asked for already)
READ the documentation -- for unless you do that, I can assure you of one fact -- you will FAIL miserably in your implementation that uses partition due to lack of understanding of how it really works.
Eyes Wide Shut, Now open...
VJ, April 24, 2003 - 1:07 pm UTC
Tom,
I guess i have read too much documentation on internet, that towards the end of the day i have started confusing my self every step of the way. Now, I got what i wanted and implemented it and it is working fine.
Thanks a lot once again,
VJ
Globally unique primary keys
A reader, January 07, 2004 - 7:06 pm UTC
Hi Tom,
For some reason I want to make the PK values in the tables unique in the whole schema. Which one is better?
1. Define one sequence and used it as the PK value for all primary key columns.
2. Define a sequence for each table and prefix it with table short name and use it as the PK. for example: 'TXN000001'.
Thanks for your comments.
BTW, the IRID and IVID columns in Designer Repository look extremely largely to me and they are not generated thru sequences. What's the internal mechanism for generating them?
January 08, 2004 - 12:41 pm UTC
or 3
3) create sequence_t1 seq start with 1 increment by 10000;
create sequence t2_seq start with 2 increment by 10000;
...
create sequence tN_seq start with N increment by 10000;
allows you to have upto 10,000 tables -- each with their own sequence, each generating unique numbers.
or
4) use sys_guid()
Not familar with designers schema -- sorry.
Mark, July 09, 2004 - 1:51 pm UTC
Tom, I am looking to use a Non Unique PK on a table for a OLTP application. The table in question is a child table of the main header table and will always be accessed via the Header_id only. All rows for a given header_id will always be returned. Each header row will have between 1 to maybe 15 child records added over time. And the Header table will grow approx 150K/year
I have the option of using date field as part of the PK to make it Unique but whats the point. I will never use it in the WHERE statement. All queries will be with joining with the header table.
Here is what it looks like.
CREATE TABLE T1
(HEADER_ID NUMBER(15),
FK1 NUMBER(15),
FK2 NUMBER(2),
TEXT1 VARCHAR2(50),
TEXT2 VARCHAR2(50),
DATE1 DATE);
CREATE TABLE T2
(HEADER_ID NUMBER(15),
ACT_DATE DATE
CODE1 NUMBER(1),
USER_ID NUMBER(15)
TEXT3 VARCHAR2(255));
Thanks for your wisdom...
July 09, 2004 - 2:03 pm UTC
"Non Unique PK" is "an oxymoron"
guess what you are saying is "my table won't have a primary key" -- which is technically fine - i might use a different name from HEADER_ID in that table as it would seem to denote "unique" by name.
Mark, July 09, 2004 - 1:52 pm UTC
Sorry,
Forgot to add. DB Version 9.2.0.5
Thanks
why we create non-unique index for PK in DWH
A reader, August 24, 2004 - 8:05 am UTC
Hi
From the original post where he says that he creates non-unique index for PK.
What is the point of doing this? We disable our indexes before data load and I dont see the benefit of using nonunique index for PK
thx
August 24, 2004 - 8:53 am UTC
ok, so you disable a unique index, what then? you cannot insert into it...
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> create unique index t_idx on t(x);
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter index t_idx unusable;
Index altered.
ops$tkyte@ORA9IR2> alter session set skip_unusable_indexes=true;
Session altered.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
insert into t values ( 1 )
*
ERROR at line 1:
ORA-01502: index 'OPS$TKYTE.T_IDX' or partition of such index is in unusable
state
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop index t_idx;
Index dropped.
ops$tkyte@ORA9IR2> create index t_idx on t(x);
Index created.
ops$tkyte@ORA9IR2> alter index t_idx unusable;
Index altered.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
1 row created.
So, the advantage is you can easily disable the index, load the data and rebuild the index after the load -- potentially increasing the load performance, without risk of LOSING an index (i'm not a fan of drop index, load, create index -- you will lose an INDEX some day and spend a week wondering "why is the system so slow" -- with the unusable index, you'll know immediately -- the first time a query goes to use the index -- that it is broken)
To explain why you may want to use this feature
A reader, October 12, 2004 - 7:17 am UTC
Often in a datawarehouse you range partition the fact tables on date in order to give a rolling window. The primary key sometimes does not include the date and hence it is unpartitioned or globally partitioned (e.g. a telecoms model where the date/time only goes down to the second and you may have more then one call in the same second so you use a sequence number)
If you make your index partitions unusable you will have to rebuild your primary key in full as you probably will not be able to determine the partitions effected by the change.
If you have a large number of local bitmap indexes on your fact table and are doing historical corrections it is often much faster to do a delete of the data to be replaced then a sqlloader direct load (as the indexes get deferred). This forces a delete/insert approach to an update. (remember to rebuild the indexes later as they may have grown alarmingly)
If the delete fails (we all do maintenance and make mistakes) you get duplicate entries in the primary key and the whole index becomes unusable. You are now faced with finding and removing these duplicates without an index.
Using a non-unique index as a base for the primary key constraint solves this problem and reduces the down-time.
October 12, 2004 - 8:28 am UTC
but you cannot -- you do understand that right?
think about it... think about what would have to happen if you used a locally partitioned, non-unique index on the primary key (and the primary key is not the partition key) -- and you insert. Think about what would physically have to happen (eg: all other local index partitions would have to be necessarily LOCKED and INSPECTED -- and kept locked. EG: one session and one session only could ever modify such a table at a time - no parallel, no nothing)
(and you have the ability to maintain global indexes during partition operations -- thusly ELIMINATING downtime, not reducing it -- but getting rid of it)
Unique Constraint W/O Unique Index
robert, December 02, 2004 - 12:10 pm UTC
8.1.7.4/CBO
I have a Global Temp T (That has NO INDEX) that my procedure inserts 1 ~ 2000 "transaction id" at first.
Step#2 I eliminate duplicates.
Afterwards, several JOIN-UPDATEs of this GTT to other tables based on this "transaction_id"
I'm tuning this proc now and I want to elimate Step#2.
Can I have a unique constraint WITHOUT getting an unique index ?
Because I read your "Index is NOT always good" writing
and think, maybe incorrectly, that index lookup on this GTT of this size is counterproductive and should go full-scan.
thanks
December 02, 2004 - 12:26 pm UTC
you should use a unique constraint -- it'll be faster than doing 2,000 "lookups"
PK using local unique index
Sami, April 28, 2005 - 11:41 pm UTC
Dear Tom,
Could you please explain the lockinh stuff --When we have primary key enabled using local partitioned unique index.
Earlier in this thread you mentioned that
<Asktom>
say session 1 does:
insert ( 4, 200112 );
and session 2 does
insert ( 4, 200111 )
and session 3 does
insert ( 4, 200110 )
Now, tell me what happens.... All of these go on at the same time bear in mind.
Unless you locked the partions pertaining to 200111 and 200110 when you
modified the index associated with partition 200112 -- what would prevent
sessions 2 and 3 from creating another empno = 4?
And further, if session 1 did not scan these indexes -- how would session 1 know
that empno = 4 doesn't already exist?
</Asktom>
SQL> create table p(id number,year number,nonkeycol varchar2(100))
partition by
range(year)
(
partition part_a values less than (2000),
partition part_b values less than (2001),
partition part_c values less than (2002),
partition part_max values less than (maxvalue)
);
Table created.
SQL> create unique index p_idx1 on p(id,year) local;
Index created.
SQL> alter table p add constraint p_pk primary key(id,year) using index local;
Table altered.
SQL> desc p
Name Null? Type
----------------------------------------- -------- ---------------
ID NOT NULL NUMBER
YEAR NOT NULL NUMBER
NONKEYCOL VARCHAR2(100)
---------SESSION 1-----------------------
SQL> insert into p values(1,1999,'part_a');
1 row created.
SQL> insert into p values(1,2000,'part_b');
1 row created.
SQL>
------------END----------------------------
------------SESSION 2 ------------------------
SQL> insert into p values(1,2001,'part_c');
1 row created.
------------END----------------------------
I don't see any lock on part_c while I am inserting value "1" into part_a or part_b.
April 29, 2005 - 8:04 am UTC
the concept was "EMPNO MUST BE UNIQUE", "TABLE PARTITIONED BY YEAR"
You cannot create a locally partitioned UNIQUE index on EMPNO in this case. In order to do that, we HYPOTHETICALLY would have to lock ALL index partitions when inserting into any single one of them and check all of them for the existence of empno=4. We don't do that, unique locally partitioned indexes are not supported unless they include the partition key.
the example I had was "unique constraint on X, partition by Y"
your example is "partition by Y, unique constraint on X,Y"
ORA-14039: Still dont get it
A reader, August 03, 2005 - 4:17 pm UTC
<quote>
the example I had was "unique constraint on X, partition by Y"
your example is "partition by Y, unique constraint on X,Y"
</quote>
I still dont get it. I have a non-partitioned table with a pk of accnt_id. I create a partitioned table with a PK of (as_of_dt,accnt_id), range or list partitioned by as_of_dt.
Why cant I create a local UNQIUE index on ACCNT_ID alone? I get the ORA-14039 when I try to do this. But when I create the local unique index on (accnt_id,as_of_dt), it works fine.
Thanks
August 03, 2005 - 6:09 pm UTC
think of how the data is organized.
You have it partitioned by DT, ID
dt=01-jan-2005, id=ANY ->partition 1
dt=02-jan-2005, id=ANY ->partition 2
and so on. id=42 may appear in zero, one or more of these partitions, hence a unique LOCAL index on ID (if it were possible, which it is not) would have to lock ALL index partitions in order to insert 42 and CHECK all index partitions in order to insert 42
In other words, it would not work very well outside of a single user database.
ORA-14039: Still dont get it
A reader, August 03, 2005 - 4:20 pm UTC
"we HYPOTHETICALLY would have to lock ALL index partitions when inserting into any single one of them and check all of them for the existence of empno=4"
Why? empno is unique in a partition, so when inserting empno=4, after deciding which partition it goes into depending on the partition key, why would it lock all other index partitions?
August 03, 2005 - 6:10 pm UTC
to prevent YOU from also inserting empno=4 in some other partition of course.
We have to prevent empno=4 from being inserted into ALL partitions if you want to put it into partition=1.
ORA-14039
VA, August 03, 2005 - 9:25 pm UTC
"to prevent YOU from also inserting empno=4 in some other partition of course."
Still dont get it. Whats wrong with inserting empno=4 in some other partition? empno is unique in a partition. Across partitions, the same empno can be repeated, so why lock them?
Yes, id=42 may appear in 0, 1 or more partitions, so what? If I insert id=42 in partition 1, why do we need to check other partitions for id=42? The other partitions might contain id=42 or they might not, how is that relevant?
August 04, 2005 - 8:15 am UTC
Then, you have just totally lost me.
I thought the goal was "empno unique", that is what I was writing about.
What are *your* requirements. For they are obviously very different from what we were discussing on this page (which was a UNIQUE constraint being enforced by a LOCAL index when the constraint is on columns that are NOT part of the partition key)
ORA-14039: Still dont get it
VA, August 04, 2005 - 9:24 am UTC
"I thought the goal was "empno unique", that is what I was writing about"
Yes, that is the goal, but what I am talking about is "empno unique" PER PARTITION. See, when I think of a LOCAL INDEX I think of it as a local index segment on just ONE partition of the table. Similar to a UNIQUE index on a non-partitioned table.
What I am realizing is that
create unique index i on mytab(accnt_id) LOCAL;
is attempting to create a UNIQUE CONSTRAINT as well in addition to the index!! ACCNT_ID is, of course, not UNIQUE to the entire table, it is unique to just a partition, so all your earlier comments are valid.
But then the question becomes, why is the CREATE UNIQUE INDEX trying to create a UNIQUE constraint? Didnt you say somewhere else on this site that indexes and constraints are really independent concepts? You can have one without the other?
Thanks
August 04, 2005 - 9:59 am UTC
what is your question here -- i'm very much lost.
That create unique index -- it is not trying to create a unique constraint, it is trying the CREATE A UNIQUE INDEX -- the contents of the INDEX must be unique. The fact that physically it would be partitioned underneath is not even relevant, the thing you are creating is an INDEX -- and an index in a single partition (old fashioned, regular stuff) should behave the same logically as an index in 2 partitions -- either the data in the INDEX is unique -- OR NOT.
You asked for unique.
ORA-14039: Still dont get it
VA, August 04, 2005 - 11:03 am UTC
"CREATE A UNIQUE INDEX -- the contents of the INDEX must be unique"
But they are unique WITHIN A PARTITION (thats what the LOCAL keyword means). So why is it trying to enforce that ACCNT_ID should be unique in the entire table?
create unique index i on mytab(accnt_id) LOCAL;
should just go to each partition, and create a regular old-fashioned index segment on THAT PARTITION only, the accnt_id IS unique in that partition.
Again, why is it trying to enforce that ACCNT_ID should be unique in the entire table when I just asked for a UNIQUE LOCAL index on ACCNT_ID?
Thanks
August 04, 2005 - 12:09 pm UTC
because you have a unique index.
forget the table
forget partitioning
you have an index.
the index is on accnt_id.
the index is unique
therefore accnt_id must be unique in the index
it does not matter if the index is stored in 1 or 100000000 partitioned, accnt_id must be UNIQUE.
partitioning is a red herring here, it is a physical implementation.
The crux of the situation is you have a UNIQUE INDEX, not partitions, not tables, not anything. You just have a UNIQUE INDEX, period. ACCNT_ID must be UNIQUE in that index.
period.
ORA-14039: Still dont get it
VA, August 04, 2005 - 1:30 pm UTC
OK I see what you are saying, hope you understand what I was trying to say.
See
</code>
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c12parti.htm#460696 <code>
That diagram makes it appear like each local index segment is totally independent of the other index segments. So, even though I have a UNIQUE index, the "uniqueness" is enforced at each index
segment level, not across ALL the index segments.
Obviously, it seems to be enforcing it across ALL the segments. I see
absolutely no reason for Oracle to do this.
This is a LOCAL index, each index segment indexes/stores rowids only for its associated local table partition, why in the world would it care about if ACCNT_ID=4 exists in some other partition? The job of the UNIQUE LOCAL index should be to index and enforce uniqueness IN THE TABLE PARTITION THAT IT IS INDEXING.
I guess what confused me is that the "sense" of the UNIQUE keyword seems to be "global" but that contradicts with the LOCAL keyword later on in the SQL statement.
Thanks
August 04, 2005 - 1:58 pm UTC
No, I don't understand why you believe that a unique index that is LOCAL should only be unique in each partition. That, does not make sense to me.
you have an index
the index either is
a) unique
b) non-unique
you pick, the INDEX is the only thing we are talking about here. That an index might be physically made up from many partitions, extents, blocks, datafiles, whatever -- not relevant.
I can draw a picture that looks like that regarding extents -- a regular nonpartition index is a segment, a segment is made up of independent, non-connected, separate extents. Should index entries be unique in an extent?
why in the world would it care about if
ACCNT_ID=4 exists in some other partition?
BECAUSE you asked for a UNIQUE INDEX ON THE ATTRIBUTE ACCNT_ID. A UNIQUE INDEX, not a unique index partition (no such syntax!!). You asked for a UNIQUE INDEX.
LOCAL is a physical implementation, a partitioning scheme.
UNIQUE is unique, there is a single unique index that physically is implemented in many partitions that happen to use a local partitioning scheme.
There is just "an index"
ORA-14039: Finally got it
VA, August 04, 2005 - 2:11 pm UTC
Ah, thanks, your persistence paid off, thanks again for bearing with me.
Your comparison with segments and extents drove it home for me (from a "why" perspective).
I guess I will go ahead and
CREATE UNIQUE INDEX i on mytab(accnt_id,as_of_dt) LOCAL;
that works fine.
Thanks again, appreciate it.
Difference between local and global indexes in enforcing uniqueness
Logan Palanisamy, August 04, 2005 - 7:39 pm UTC
Here is a demo which hopefully answers some of the questions raised on this thread. It certainly helped clear up some of my mis-conceptions regarding local indexes and uniqueness.
SQL> -- Regular non-partitioned table
SQL> drop Table t;
Table dropped.
SQL> Create Table t
2 (
3 acctid number Not Null,
4 date_of_opening date Not Null
5 );
Table created.
SQL>
SQL> create unique index t_idx on t(acctid);
Index created.
SQL>
SQL> insert into t values (1, '01-JAN-2005');
1 row created.
SQL> insert into t values (1, '02-JAN-2005');
insert into t values (1, '02-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated
SQL> insert into t values (1, '31-JAN-2005');
insert into t values (1, '31-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated
SQL> insert into t values (1, '01-FEB-2005');
insert into t values (1, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated
SQL> insert into t values (2, '01-FEB-2005');
1 row created.
SQL> insert into t values (2, '01-FEB-2005');
insert into t values (2, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated
SQL> insert into t values (2, '28-FEB-2005');
insert into t values (2, '28-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.T_IDX) violated
SQL>
SQL> select * from t;
ACCTID DATE_OF_OPE
---------- -----------
1 01-JAN-2005
2 01-FEB-2005
SQL>
SQL>
SQL>
SQL> -- Same table partitioned on date_of_opening
SQL>
SQL> drop Table pt;
Table dropped.
SQL> Create Table pt
2 (
3 acctid number Not Null,
4 date_of_opening date Not Null
5 )
6 partition by range(date_of_opening)
7 (
8 partition p_jan05 values less than
9 (to_date('01-FEB-2005','DD-MON-YYYY')),
10 partition p_feb05 values less than
11 (to_date('01-MAR-2005','DD-MON-YYYY')),
12 partition p_maR05 values less than
13 (to_date('01-APR-2005','DD-MON-YYYY'))
14 );
Table created.
SQL>
SQL> -- This index will fail because it doesn't have the partition as part of it.
SQL> create unique index pt_l_idx on pt(acctid) local;
create unique index pt_l_idx on pt(acctid) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE
index
SQL>
SQL> -- This index gets created because of the inclusion of the partition key
SQL> create unique index pt_l_idx on pt(acctid, date_of_opening) local;
Index created.
SQL>
SQL> -- Consequently it allows the same account id even within the same partion, let alone another partition. Beware.
SQL> insert into pt values (1, '01-JAN-2005');
1 row created.
SQL> insert into pt values (1, '01-JAN-2005');
insert into pt values (1, '01-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_L_IDX) violated
SQL> insert into pt values (1, '31-JAN-2005');
1 row created.
SQL> insert into pt values (1, '01-FEB-2005');
1 row created.
SQL> insert into pt values (1, '01-FEB-2005');
insert into pt values (1, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_L_IDX) violated
SQL> insert into pt values (1, '28-FEB-2005');
1 row created.
SQL>
SQL> select * from pt;
ACCTID DATE_OF_OPE
---------- -----------
1 01-JAN-2005
1 31-JAN-2005
1 01-FEB-2005
1 28-FEB-2005
SQL> select * from pt partition (p_jan05);
ACCTID DATE_OF_OPE
---------- -----------
1 01-JAN-2005
1 31-JAN-2005
SQL> select * from pt partition (p_feb05);
ACCTID DATE_OF_OPE
---------- -----------
1 01-FEB-2005
1 28-FEB-2005
SQL>
SQL> delete from pt;
4 rows deleted.
SQL> drop index pt_l_idx;
Index dropped.
SQL>
SQL> -- If you want the account-id to be really unique across table, you have no choice but to create a global index.
SQL> -- As you can see, it doesn't allow any duplicates
SQL> drop index pt_g_idx;
drop index pt_g_idx
*
ERROR at line 1:
ORA-01418: specified index does not exist
SQL> create unique index pt_g_idx on pt(acctid);
Index created.
SQL> insert into pt values (1, '01-JAN-2005');
1 row created.
SQL> insert into pt values (1, '01-JAN-2005');
insert into pt values (1, '01-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated
SQL> insert into pt values (1, '31-JAN-2005');
insert into pt values (1, '31-JAN-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated
SQL> insert into pt values (1, '01-FEB-2005');
insert into pt values (1, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated
SQL> insert into pt values (2, '01-FEB-2005');
1 row created.
SQL> insert into pt values (2, '01-FEB-2005');
insert into pt values (2, '01-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated
SQL> insert into pt values (2, '28-FEB-2005');
insert into pt values (2, '28-FEB-2005')
*
ERROR at line 1:
ORA-00001: unique constraint (LOGAN.PT_G_IDX) violated
SQL> select * from pt;
ACCTID DATE_OF_OPE
---------- -----------
1 01-JAN-2005
2 01-FEB-2005
SQL> select * from pt partition (p_jan05);
ACCTID DATE_OF_OPE
---------- -----------
1 01-JAN-2005
SQL> select * from pt partition (p_feb05);
ACCTID DATE_OF_OPE
---------- -----------
2 01-FEB-2005
SQL> spool off
reader
A reader, August 09, 2005 - 2:18 pm UTC
SQL> alter table owner.table_name drop primary key;
alter table owner.table_name drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
How do I trace the foreign keys. If I disable the foreign
key is sufficient or I need to drop the foreign keys
before dropping the primary key
August 09, 2005 - 3:25 pm UTC
select * from all_constraints where r_constraint_name = 'that primary key' and r_owner = 'that owner'
you have to drop the referencing constraints.
ops$tkyte@ORA9IR1> create table p ( x int constraint p_pk primary key );
Table created.
ops$tkyte@ORA9IR1> create table c ( x constraint c_fk references p );
Table created.
ops$tkyte@ORA9IR1>
ops$tkyte@ORA9IR1> select constraint_name, owner, table_name
2 from all_constraints
3 where r_constraint_name = 'P_PK'
4 and r_owner = user;
CONSTRAINT_NAME OWNER
------------------------------ ------------------------------
TABLE_NAME
------------------------------
C_FK OPS$TKYTE
C
ops$tkyte@ORA9IR1>
ops$tkyte@ORA9IR1> alter table p drop constraint p_pk;
alter table p drop constraint p_pk
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
ops$tkyte@ORA9IR1> alter table c disable constraint c_fk;
Table altered.
ops$tkyte@ORA9IR1> alter table p drop constraint p_pk;
alter table p drop constraint p_pk
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys
ops$tkyte@ORA9IR1> alter table c drop constraint c_fk;
Table altered.
ops$tkyte@ORA9IR1> alter table p drop constraint p_pk;
Table altered.
Thanks
A reader, August 09, 2005 - 7:48 pm UTC
Soma, March 28, 2006 - 5:54 am UTC
Dear Tom
I have one table with No Primary Key, but i have created a unique index of a particular field,
say ex. Customer table
for Cust_code field i have created the Unique index,
My question is IS there is any performance degradation when i am selecting this table with Cust_Code in the where Conditon, This table is having more than 1 lac records.
March 28, 2006 - 7:59 am UTC
the answer is always: it depends :)
In general, I'd have to say "no, the unique index in most cases is sufficient"
however, a PRIMARY KEY is more than a unique index (in fact, it need not have a unique index - that is not a requirement)
A primary key SAYS something.
A primary key is meta-data
A primary key's existence is used by the optimizer when optimizing.
A primary key is more than unique - it is also NOT NULL
So, even if this has zero effect on your particular runtime performance - you have still done it wrong. It is a primary key, state that fact (since you already have the index, adding the key will do nothing more than add more information to your database! What a bonus. It'll let you create FOREIGN KEYS to this table too!!!!)
Create Unique Constraint Without Unique Index
Saravanan Ramalingam, May 09, 2006 - 9:02 am UTC
Hi Tom,
Is it possible to create Unique Constraint on a table without creating an unique index.
May 09, 2006 - 9:53 am UTC
yes.
It can use a non-unique index. As, well, demonstrated above in the original answer ;)
SQL Expert contradicts
Anindya Mitra, May 24, 2006 - 6:14 am UTC
Hi Tom,
Please check the link below:
"</code>
http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci1168808_tax301455,00.html?track=NL-93&ad=547742" <code>
It says...
"Question - What does "the primary key of the table is enforced using a non-unique index" really mean?
This question posed on 23 February 2006
It means you got that nugget of information (hint: it's wrong) from an unverified source. <grin>
NOTE: If this was, by some remote chance, something that I wrote, then please let me know which article. I'll see to getting it corrected right away. It's obviously just a typo.
A primary key is implemented using a unique index, with a not null constraint..."
The Question was posted on Feb,06; so the answer is also recent.
May 24, 2006 - 7:17 am UTC
It (the quoted article) is obviously wrong, the example is staring at you right above.
You cannot contradict an example such as the above.
Smaller example:
ops$tkyte@ORA10GR2> drop table t;
Table dropped.
ops$tkyte@ORA10GR2> create table t
2 ( x int primary key using index ( create index t_idx on t(x) ) )
3 /
Table created.
ops$tkyte@ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX NONUNIQUE
ops$tkyte@ORA10GR2> drop table t;
Table dropped.
ops$tkyte@ORA10GR2> create table t
2 ( x int primary key using index ( create UNIQUE index t_idx on t(x) ) )
3 /
Table created.
ops$tkyte@ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
T_IDX UNIQUE
The "currency" of an answer is no indication of it's correctness.
Also, the article says:
<quote>
That's why the database optimizer automatically builds an index when you declare a primary key.
</quote>
that is technically not true.
ops$tkyte@ORA10GR2> create table t
2 ( x int,
3 y int,
4 z int
5 )
6 /
Table created.
ops$tkyte@ORA10GR2> create index MY_IDX on t(x,y,z);
Index created.
ops$tkyte@ORA10GR2> alter table t add constraint t_pk primary key(x);
Table altered.
ops$tkyte@ORA10GR2> select index_name, uniqueness from user_indexes where table_name = 'T';
INDEX_NAME UNIQUENES
------------------------------ ---------
MY_IDX NONUNIQUE
See, the database did not create yet another index on T(x) - since an index that was good enough already exists.
Uniqueness in partitioned table.
Aru, September 24, 2008 - 2:16 am UTC
Hi Tom,
We have been asked to convert an existing table to a partitioned table.
CREATE TABLE SOURCE.TDDB_ROCMO_CONPOS_HIS1 (
ID NUMBER(10) NOT NULL,
TRADING_DATE DATE NOT NULL,
TRADING_PERIOD
NUMBER(10) NOT NULL,
NODE VARCHAR2(8) NOT NULL,
NET_POSITION NUMBER(10),
MARKET_LOAD NUMBER(10),
CONTRACT_LOAD NUMBER(10),
SPOT_LOAD NUMBER(10),
CALCULATION_DATE DATE NOT NULL,
LAST_UPDATE_TIME DATE NOT NULL)
PARTITION BY RANGE (TRADING_DATE)
(PARTITION TDDB_ROCMO_CONPOS_HIS1_P1 VALUES LESS THAN (TO_DATE('2008-1-1','YYYY-MM-DD')) TABLESPACE TDDB_LOG_DATA ,
PARTITION TDDB_ROCMO_CONPOS_HIS1_P2 VALUES LESS THAN (TO_DATE('2009-1-1','YYYY-MM-DD')) TABLESPACE TDDB_LOG_DATA ,
PARTITION TDDB_ROCMO_CONPOS_HIS1_P3 VALUES LESS THAN (TO_DATE('2010-1-1','YYYY-MM-DD')) TABLESPACE TDDB_LOG_DATA);
CREATE INDEX SOURCE.IDX_TDDB_ROCMO_CONPOS_HIS1_TRADING_DATE ON SOURCE.TDDB_ROCMO_CONPOS_HIS1 (TRADING_DATE) LOCAL
insert into tddb_rocmo_conpos_his1 (select * from tddb_rocmo_conpos_hist);
CREATE UNIQUE INDEX TDDB_CONPOS_ROCMO_HIS_UK on TDDB_ROCMO_CONPOS_HIS1(ID,TRADING_DATE) LOCAL(
partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX);
ALTER TABLE tddb_rocmo_conpos_his1 ADD CONSTRAINT tddb_rocmo_conpos_his1_pk primary key(ID,TRADING_DATE) using index local (
partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX);
Till here everything works fine. But when we go to create a composite unique index as below it gives error:-
1 ALTER TABLE tddb_rocmo_conpos_his1 ADD CONSTRAINT tddb_rocmo_conpos_comp_hi
s1_uk unique (TRADING_DATE,trading_period,node,net_position,market_load,contract
_load,spot_load) using index local (
2 partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
3 partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
4* partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX)
SQL> /
ALTER TABLE tddb_rocmo_conpos_his1 ADD CONSTRAINT tddb_rocmo_conpos_comp_his1_uk unique (TRADING_DATE,trading_period,node,net_position,market_load,contract_load,spot_load) using index local (
ERROR at line 1:
ORA-02299: cannot validate (SOURCE.TDDB_ROCMO_CONPOS_COMP_HIS1_UK) - duplicate
keys found
The same constraints work fine in heap organized table and the data is exactly the same. Please can you explain if this is specific to partitioned tables or is my methodology wrong as I am trying to create local indexes here?
September 24, 2008 - 7:18 pm UTC
do exceptions into - see what data is bad - for you see, unless I can reproduce it... I cannot see it....
I don't have your data - but an exceptions into might be "telling"
Unique index.
Aru, September 24, 2008 - 8:12 pm UTC
Thanks Tom,
Will do the exceptions into and check the results.
One real basic question I have here:-
I got the DDL to create the table using TOAD from production.
Instead of doing:-
CREATE UNIQUE INDEX TDDB_CONPOS_ROCMO_HIS_UK on TDDB_ROCMO_CONPOS_HIS1(ID,TRADING_DATE) LOCAL(
partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX);
ALTER TABLE tddb_rocmo_conpos_his1 ADD CONSTRAINT tddb_rocmo_conpos_his1_pk primary key(ID,TRADING_DATE) using index local (
partition TDDB_ROCMO_CONPOS_HIS1_P1 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P2 TABLESPACE TDDB_LOG_INDEX,
partition TDDB_ROCMO_CONPOS_HIS1_P3 TABLESPACE TDDB_LOG_INDEX);
Won't just creating a primary key on the two columns give the same result? Why first create a unique index on the columns and then add primary key, why not just create a primary key and it would create the unique index as well?
Regards,
ARU.
Constraints
Aru, September 24, 2008 - 10:35 pm UTC
Hi Tom,
Just did a quick test where tddb_rocmo_conpos_his is the original table. I created two tables tddb_rocmo_conpos_his2 and tddb_rocmo_conpos_his3, where tddb_rocmo_conpos_his3 is partitioned. But when after creating the constraints the result is different, as below.
Don't think it should happen even if there are duplicate data in the table. Please can you check?
1 CREATE TABLE TDDB_ROCMO_CONPOS_HIS2 (
2 ID NUMBER NOT NULL,
3 TRADING_DATE DATE NOT NULL,
4 TRADING_PERIOD NUMBER NOT NULL,
5 NODE VARCHAR2(8) NOT NULL,
6 NET_POSITION NUMBER,
7 MARKET_LOAD NUMBER,
8 CONTRACT_LOAD NUMBER,
9 SPOT_LOAD NUMBER,
10 CALCULATION_DATE DATE NOT NULL,
11 LAST_UPDATE_TIME DATE NOT NULL,
12 CONSTRAINT TDDB_RCPH_PK2 PRIMARY KEY(trading_date,ID) USING INDEX TABLE
SPACE TDDB_LOG_INDEX,
13 CONSTRAINT TDDB_RCPH_UK2 UNIQUE(TRADING_DATE, TRADING_PERIOD, NODE, NET
_POSITION, MARKET_LOAD, CONTRACT_LOAD, SPOT_LOAD) USING INDEX
14* TABLESPACE TDDB_LOG_INDEX )
SQL> /
Table created.
SQL> insert into TDDB_ROCMO_CONPOS_HIS2 (select * from TDDB_ROCMO_CONPOS_HIS);
9305249 rows created.
1 CREATE TABLE TDDB_ROCMO_CONPOS_HIS3 (
2 ID NUMBER(10) NOT NULL,
3 TRADING_DATE DATE NOT NULL,
4 TRADING_PERIOD NUMBER(10) NOT NULL,
5 NODE VARCHAR2(8 byte) NOT NULL,
6 NET_POSITION NUMBER(10),
7 MARKET_LOAD NUMBER(10),
8 CONTRACT_LOAD NUMBER(10),
9 SPOT_LOAD NUMBER(10),
10 CALCULATION_DATE DATE NOT NULL,
11 LAST_UPDATE_TIME DATE NOT NULL,
12 CONSTRAINT TDDB_ROCMO_CONPOS_HIS3_PK3 PRIMARY KEY(trading_date,I
D) USING INDEX TABLESPACE TDDB_LOG_INDEX,
13 CONSTRAINT TDDB_RCPH_UK3 UNIQUE(TRADING_DATE, TRADING_PERIOD,
NODE, NET_POSITION, MARKET_LOAD, CONTRACT_LOAD, SPOT_LOAD) USING INDEX
14 TABLESPACE TDDB_LOG_INDEX)
15 PARTITION BY RANGE (TRADING_DATE) (
16 PARTITION TDDB_ROCMO_CONPOS_HIS3_P1 VALUES LESS THAN (TO_DATE('2
008-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPA
CE TDDB_LOG_DATA ,
17 PARTITION TDDB_ROCMO_CONPOS_HIS3_P2 VALUES LESS THAN (TO_DATE('2
009-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPA
CE TDDB_LOG_DATA,
18* PARTITION TDDB_ROCMO_CONPOS_HIS3_P3 VALUES LESS THAN (TO_DATE('2
010-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPA
CE TDDB_LOG_DATA)
SQL> /
Table created.
SQL> insert into TDDB_ROCMO_CONPOS_HIS3 (select * from TDDB_ROCMO_CONPOS_HIS);
insert into TDDB_ROCMO_CONPOS_HIS3 (select * from TDDB_ROCMO_CONPOS_HIS)
*
ERROR at line 1:
ORA-00001: unique constraint (SOURCE.TDDB_RCPH_UK3) violated
Regards,
Aru.
September 25, 2008 - 3:10 pm UTC
Aru,
unless I can reproduce it - and since I don't have your data - I cannot right now, I'll just ask you to do what I asked you to do - in the hopes we can get a reproducible test case. Use the exceptions into, find the offending row(s), create a tiny - teeny tiny - complete test case - then, if it is a bug, we are ready to go to support. If it is not a bug, it will be obvious what it is.
I haven't really parsed your sql here too intensely - I'm waiting for the teeny tiny test case, exceptions into will be the *first* step towards that
version and OS information would be useful as well.
it looks like a bug, but I'll need something to reproduce with. The question is whether
a) the non-partitioned table is the bug
b) the partitioned table is the bug
eg: is there actually duplicate data that one is not catching, or is there no duplicate data that the other is erroneously reporting.
partitoned local index key not in PK
Walker, November 27, 2008 - 3:25 pm UTC
Hi ,
I have a table partitioned on col b but the PK is col a . Is it possible to use local partitioned index (unique / non unique )for PK ?
When I try to create a local unique index on a it disallows saying that b should be a sub set of the local unique index .
When I create a non unique index on a , it succeeds but when I try to enforce PK on a it probably tries to create a global index ............ I don not wish to use Global Index for obvious truncate reasons ..... does it mean that in order to have PK on locally partitioned indexes the partitioned key needs to be in the PK ?
November 28, 2008 - 5:30 pm UTC
no, it is not.
you have to create a global index for that.
Your truncate reasons are far from obvious? In current releases of the software you do not have to ever lose availability of the data due to a global index becoming unusable if you do not want to. So, what is the "obvious" reason?
a very long unique key
A reader, January 13, 2012 - 7:35 am UTC
Dear Tom,
We have a very important table say t1(id, col1,col2,...................,col35)
where id is a primary key.
Actually, we are communicating information about that table to several external applications which are giving us feedback again using this primary key (id) as a matching criteria. We would like to change our (id) matching criteria by a functionnal unique key. The problem is that our functionnal unique key contains 10 columns uk(col1,col2,.....col10);
I am not very confident with creating a unique key having 10 columns in it.
Do you have any other proposition?
Thanks in advance
I searched your site about a question looking
January 17, 2012 - 9:27 am UTC
We would like to change our (id) matching criteria by a
functionnal unique key.
why? what goal are you attempting to meet?
given that ID already exists, and that communicating with external sources is already done via ID and ID is a single column (which is going to be easier all around than a 10 column primary key) - why wouldn't you stick with ID?
what is the goal behind making this change, why are you thinking about doing this? If we understand that, we might be able to suggest something.
A reader, January 18, 2012 - 2:06 am UTC
Thank you very much for taking time to answer my question
"what is the goal behind making this change, why are you thinking about doing this? If we understand that, we might be able to suggest something"
The client for which I am working now, wants that all communications with externals applications, from now and on, start to be done via a functional key. He doesn't want us to continue using this primary key id.
That would have been easy for us if we have identified a unique fonctional key with 2 or 3 columns. Unfortunately, the sole unique functional key we've identified is formed with 10 columns. And I am not so confident with creating this 10 columns unique key; hence my question.
Best regards
January 18, 2012 - 7:34 am UTC
tell this client
a) there is no such term as a functional key, I believe you mean natural key.
b) you are the client, you are not the software developer
c) it would not make sense to make this change
d) but if you want to pay us 1,000,000 dollars, we'll gladly cut you a custom version that will not run as well as the existing code.
why would a client care about such a thing? They either buy your product or not and your product works technically the way it works.
It would not make sense to me (given the above information - that is all I'm working from) to change an interface from a nice single column surrogate key to an unwieldy 10 column 'natural key'.
(are all 10 of these columns defined as NOT NULL?)
A reader, January 19, 2012 - 3:31 am UTC
Yes, all of thoses 10 columns are declared to be NOT NULL. Does this change anything relatively to your last answer?
Definitely I agree with you.
Thanks a lot
January 19, 2012 - 5:29 am UTC
I was hoping one of them would be NULLABLE. that would preclude them from being a primary key.
Good luck, I would just tell them "no, it doesn't work that way" personally. Unless they want to personally foot the bill (plus some) to do the work to make things that much harder for everyone.
A reader, April 26, 2012 - 2:38 pm UTC
Hi tom,
oracle use existing index to polish the unique/primary key constraint.
can you show in which scenario it will be apllicable?
Thanks
April 26, 2012 - 3:37 pm UTC
ops$tkyte%ORA11GR2> create table t as select * from all_users;
Table created.
ops$tkyte%ORA11GR2> create index t_idx on t(user_id);
Index created.
ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(user_id);
Table altered.
ops$tkyte%ORA11GR2> select index_name from user_indexes;
INDEX_NAME
------------------------------
T_IDX
ops$tkyte%ORA11GR2>
not sure what you mean truly - but there is an example of the database using an existing index - T_PK needs an index to enforce itself, the only index is the one I created before the constraint was there.