This really helped me to make my decision, Tom. Thanks.

Since there will be quite a few tables linked via foreign key, I'll go with the new, sequentially-numbered field as primay key.

Well on Papers good solution..but sequence numbers does give u skips..incase trigger fails for some reasons...BUT IF this not a point i mean a skip or two is tolerable then yes..it's ok. Why are u hasitant to make a Composite Primary key! it works just fine.

I am interested in your opinion with regard to the insistence by some clients to specify the use of what I have heard referred to as "smart numbers" which are just a composite key all stuck together in one column.

A simple example would be this Incident ID:

03-40123 Format: FY-XNNNN

where:

FY = 2 digit fiscal year
X = 1 digit incident type
NNNN = 4 digit sequence of inc. type within FY


Now, I personally try to avoid this kind of identifier (system generated or otherwise) like the plague for several of what I consider to be obvious reasons:

* data entered initially was wrong and made the key wrong
(i.e.: incident type is actually 3, not 4)
too bad, the world already "knows" this incident as 03-40123

* it makes assumptions about upper limits:
1) there will never be >10,000 incident type 1’s in one FY
2) forget about adding an eleventh incident type
3) two digit year –- been there!

* they're cumbersome to implement

* what good does knowing FY and incident type at a glance do anyway?


I’ve had this problem come up at times, usually when upgrading from a "legacy" and/or paper-based system where users are resistant to changing their numbering scheme. Often, I have been able to convince (coerce?) the client to use a simple sequence, but not always.

I know you’re not big on rules of thumb but can things like the nature of the data (static reference, transactional, etc.) or a need for the key to be "memorable" give merit to this type of key.

Under what conditions do you stand for a design that makes use of a composite key column, like above, to identify data?

Would you be inclined to use a surrogate key and let the customer have their smart number whatever way they want it or would you (perhaps strongly) suggest a simple sequence approach?

As usual, your solution is as clear as can be. I especially like the function-based index part!

However, that doesn’t quite protect me from a bit of an "address change" problem.

As I (rather clumsily) tried to state in first bullet, once fy, incident_type, and goofy_number are known and a row is inserted, then their_number is implicitly defined. At this point, perhaps their_number is printed on reports, given out to involved parties, etc.

When it is found that, say incident_type, was entered wrong initially and is updated then the database’s view of their_number and that shown on reports, held by involved parties, etc., are a no match.

I can see basing their_number on columns that are not updatable (thru whatever means) but that may not meet "their" need.

I could build their_number at insert time, put it in its own column (UK, not PK) and never update it again even if its subcomponents change.


I guess my bottom-line question is this:

Even as the "expert" hired to provide solutions with solid data definitions, am I overstepping my bounds (and/or wasting time) when I use (sometimes) hours trying to dissuade clients from their_number in favor of MY number, a simple sequence?

We have two tables like this:

create table t1(c1 number, c2 varchar2(50),
constraint t1p primary key(c1, c2));

create table t2(c1 number, c2 varchar2(50), c3 number,
constraint t2p primary key(c1, c2, c3),
constraint t2f foreign key (c1, c2)
references t1(c1, c2));

insert into t1(c1, c2)
values(1, 'c2');


insert into t2(c1, c2, c3)
values(1, 'c2', 3);

Now we need to update c2 of t1. Are there any ways to update this primary key
besides your suggestion of surrogate key?

I am not aware of update primary key cascade SQL command.

Best,

Sean

Hi Tom,

The package works amazingly well.

The questions:

(1) After I update the primary key, how do I dissociate that table from package (I still need the package for other tables, so I can not drop the package).

(2) Using this package seems be the best solution for us since we don’t need to change any code. Besides the performance, any other disadvantages?

(3) The solution of ‘deferrable constraints’ you mentioned is that I add deferrable to foreign key tables, so I can update primary key first, then foreign key. Is this what you mean? Here is the sample:

create table t1(c1 number, c2 varchar2(50),
constraint t1p primary key(c1, c2));

create table t2(c1 number, c2 varchar2(50), c3 number,
constraint t2p primary key(c1, c2, c3),
constraint t2f foreign key (c1, c2)
references t1(c1, c2) deferrable initially deferred);



Best,

Sean

(1)  First time after package installed and t1, t2, t3 table created, I need to run this command first in order to update primary key:

exec update_cascade.on_table('t2')

But if I open a new session after this session, I was able to update primary without exec this command
'exec update_cascade.on_table('t2')'.  But I don't want people to update this PK all the times.

Here is the sample:

SQL> connect  UCDEMO/UCDEMO@e2rs
Connected.
SQL> update t2
  2  set b=900
  3  where b = 6;     -- update primary key without 'exec update_cascade.on_table('t2')'

1 row updated.
SQL> 

(2) Thanks,  We will try to correct the design flaw.

 

Thanks so much for your help.

Sean

what are the pros and cons of using sys_guid()(suspect
space may be an issue with this approach?) Also with
sys_guid() you can just put it in the default clause
( no need for the auto-increment trigger".

Obviously we dont care about "gaps" here:)

Hi Tom
In our case, we are using sys_guid instead of sequence
(we have some requirement to make the primary keys
globally unique) - anyways, I thought I would run
benchmarks and share results with all:

The results show that:

1. elapsed time wise sequences are faster (I used cache
option with a cache of 5000) - I did get some wild result
variation - sequences were like 52% to 90% faster than
sys_guid in different runs.
I suppose the reason that are faster is due to the sga caching of sequences , right? However,I was wondering what makes sys_guid slower - what is the internal mechanics for
generating sys_guid?)

2. Of course sequences took less space (around 1/2 of what
sys_guids occupied)

3. sys_guid consumed less latches however - mainly
following latches:

STAT...session logical reads 319,026 317,195 -1,831
STAT...db block gets 318,025 315,680 -2,345
LATCH.cache buffers chains 1,398,729 1,366,857 -31,872
LATCH.library cache 407,043 207,830 -199,213
LATCH.sequence cache 300,040 0 -300,040

Could you please explain each of them - I think the
latter two are probably the sequences latching to
get and set the next val for my session.
In particular,
what results in higher db block gets and cache buffer chains for sequences? From "Oracle reference" cache buffer
chains - refers to a hot block contention meaning
sequences result in accessing of the same block
many times (potentially of the internal table used
to store sequences (seq$ table)?

test@apps> @schema
test@apps> drop sequence my_seq;

Sequence dropped.

Elapsed: 00:00:00.09
test@apps> create sequence my_seq cache 5000;

Sequence created.

Elapsed: 00:00:00.02
test@apps> drop table t_guid ;

Table dropped.

Elapsed: 00:00:04.51
test@apps> create table t_guid
2 (
3 id RAW(16) default sys_guid() not null primary key,
4 value number
5 );

Table created.

Elapsed: 00:00:00.09
test@apps>
test@apps>
test@apps> drop table t_seq ;

Table dropped.

Elapsed: 00:00:01.44
test@apps> create table t_seq
2 (
3 id int not null primary key,
4 value number
5 );

Table created.

Elapsed: 00:00:00.08
test@apps>
test@apps>
test@apps> @data
test@apps> create or replace package run_benchmark_pkg as
2 g_number_of_records constant integer := 100000 ;
3
4 procedure insert_with_seq;
5 procedure insert_with_guid;
6 end;
7 /

Package created.

Elapsed: 00:00:00.09
test@apps> create or replace package body run_benchmark_pkg as
2 procedure insert_with_seq
3 is
4 begin
5 for i in 1 .. g_number_of_records
6 loop
7 insert into t_seq values( my_seq.nextval, i);
8 end loop;
9 end;
10
11 procedure insert_with_guid
12 is
13 begin
14 for i in 1 .. g_number_of_records
15 loop
16 insert into t_guid (value) values( i);
17 end loop;
18 end;
19
20 end;
21 /

Package body created.

Elapsed: 00:00:00.16
test@apps>
test@apps> begin
2 runstats_pkg.rs_start;
3 run_benchmark_pkg.insert_with_seq;
4 runstats_pkg.rs_middle;
5 run_benchmark_pkg.insert_with_guid;
6 runstats_pkg.rs_stop(200);
7 end;
8 /
Run1 ran in 5728 hsecs
Run2 ran in 7337 hsecs
run 1 ran in 78.07% of the time

Name Run1 Run2 Diff
STAT...calls to get snapshot s 100,582 100,847 265
STAT...commit cleanouts 423 708 285
STAT...commit cleanouts succes 423 708 285
LATCH.list of block allocation 567 858 291
STAT...immediate (CURRENT) blo 372 674 302
STAT...calls to kcmgcs 396 718 322
LATCH.transaction allocation 864 1,266 402
STAT...consistent gets - exami 670 1,130 460
STAT...consistent gets 1,001 1,515 514
STAT...free buffer requested 2,916 3,775 859
STAT...change write time 2,455 3,775 1,320
STAT...session logical reads 319,026 317,195 -1,831
STAT...dirty buffers inspected 1,996 3,991 1,995
STAT...free buffer inspected 1,996 3,991 1,995
LATCH.redo allocation 205,074 207,309 2,235
STAT...redo entries 203,898 206,150 2,252
STAT...db block gets 318,025 315,680 -2,345
LATCH.cache buffers lru chain 4,826 7,585 2,759
STAT...db block changes 408,338 411,971 3,633
LATCH.checkpoint queue latch 8,652 12,829 4,177
LATCH.cache buffers chains 1,398,729 1,366,857 -31,872
LATCH.library cache 407,043 207,830 -199,213
LATCH.sequence cache 300,040 0 -300,040
STAT...redo size 49,988,292 56,368,192 6,379,900

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
3,946,936 3,426,263 -520,673 115.20%

PL/SQL procedure successfully completed.

Elapsed: 00:02:11.30
test@apps> exec show_space ( 'T_GUID')
Unformatted Blocks ..................... 30
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 1
FS4 Blocks (75-100)..................... 31
Full Blocks ..................... 370
Total Blocks............................ 512
Total Bytes............................. 4,194,304
Total MBytes............................ 4
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 9
Last Used Ext BlockId................... 177,800
Last Used Block......................... 64

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.27
test@apps> exec show_space ( 'T_SEQ')
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 1
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 40
Full Blocks ..................... 203
Total Blocks............................ 256
Total Bytes............................. 2,097,152
Total MBytes............................ 2
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 9
Last Used Ext BlockId................... 176,776
Last Used Block......................... 128

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.22
test@apps> spool off

Please keep up the awesome work!!

Hi Tom,
In the oracle 9.0.1.1.1 documentation says that "A composite primary key cannot have more than 32 columns". But I have tried with 33 columns its working fine. What is the reason exactly?
This is table I have tried.
create table Test(x1 number(2) ,x2 number(2) ,x3 number(2) ,x4 number(2) ,x5 number(2) ,x6 number(2) ,x7 number(2) ,x8 number(2) ,x9 number(2) ,x10 number(2),x11 number(2),x12 number(2),x13 number(2),x14 number(2),x15 number(2),x16 number(2),x17 number(2),x18 number(2),x19 number(2),x20 number(2),x21 number(2),x22 number(2),x23 number(2),x24 number(2),x25 number(2),x26 number(2),x27 number(2),x28 number(2),x29 number(2),x30 number(2),x31 number(2),x32 number(2),x33 number(2),x34 number(2),x35 number(2),x36 number(2),x37 number(2),x38 number(2),x39 number(2),x40 number(2),
primary key( x1,x2,x3,x4,x5,x6,x7,x8,x9,x10,x11,x12,x13,x14,x15,x16,x17,x18,x19,x20,x21,x22,x23,x24,x25,x26,x27,x28,x29,x30,x31,x32,x33))
/

Tom
Effective Oracle By Design -- When is your Book available in India?

Surrogate Key
- What is this key used for and how is it helpful .. I do not get the cocept of this surro... key
Thanks for your patience

In our case we are using sys_guid because we want
to avoid clashes when moving data from one db to another.
I am still not sure about the entire rationale - but
it seems to be that when you import and export data
from one db - db1 to another db db2, if there are sequences
then we will run into conflicts (since the same sequence
is created in both schemas.

Have you faced this situation? Is there an elegant solution
for this problem apart from using sys_guid
instead of sequences, This is different from the case
where we have a known number of sites and each site can
start the sequence at a different number.

"non-overlapping sequences generated at upto 1000 sites. "

I went through that solution in your site somewhere.
It is very elegant - but you have to know in advance the
number of sites. In our case the requirement is that we
can have any local database where the schema can be populated and then subsequently "merged" into a central
db through export/import.

Ideally, the same schema install script should always work
without creating clashes in any database.

*sigh* - I guess short of always having a central table
storing starting points of sequences and having serialized access - there does not seem to be a good alternative...

Thank you so much!!

There are a couple of assumptions here (such as there being a manageable amount of data to transfer in this way), and this involves writing some code, but:

You could write some extraction routines to pull out the data from individual tables in a format that can be used to define an external table in your target DB. This would give you a file per table (per source DB).

The filenames will need to encode the tablename and the source database. This information can be stored in a table in the target DB to allow you to manage the creation of a new source DB with a data change.

These files are then used to populate the target DB:

You will need to know the mapping between your target tables, columns and local sequences that are used to populate these tables. As well as how the filename maps to the table / source DB.

A procedure could then directly query your data from the external tables and dynamically generate insert statements using the appropriate sequence in the correct place in the insert ie;

insert into * select seq.next_val, col1, col2 from external table.

I am assuming that the target DB is live, and that you do not need to transfer index / constrainst etc.

I am not extremely familiar with external tables - may
be that is why the solution looks more complicated:)
I also want to avoid dynamically generated inserts.

What do you think of the following idea?

When installing the product schema sequences are created.
Our attempt is to have a unique increment number a given installation. This number can be based on the current date and time.
Since we can't generate the sequences as above using
NDS, this would have to be done using sql - spooled
into a file and then executed.

following shows the logic of generating create statements
with unique increments.

test@apps> @t
test@apps> select 'create sequence my_sequence start with 1 increment by ' || inc || ';'
2 from
3 (
4 select julian_date + hour_minute_second inc
5 from
6 (
7 select to_number(to_char(sysdate+1, 'J')) julian_date ,
8 to_number( to_char(sysdate, 'hh24MISS')) hour_minute_second
9 from dual
10 )
11 );

create sequence my_sequence start with 1 increment by 2595011;

Elapsed: 00:00:00.01
test@apps> @t
test@apps> select 'create sequence my_sequence start with 1 increment by ' || inc || ';'
2 from
3 (
4 select julian_date + hour_minute_second inc
5 from
6 (
7 select to_number(to_char(sysdate+1, 'J')) julian_date ,
8 to_number( to_char(sysdate, 'hh24MISS')) hour_minute_second
9 from dual
10 )
11 );

create sequence my_sequence start with 1 increment by 2595014;

Elapsed: 00:00:00.00
test@apps>

This should work - what do you think Tom, Matt?

Menon:)

And if you are concerned with the increment being
rather high - you can of course subtract a known
constant (e.g. 2500000 from the value you got to have
lower increments but always different ones for each
install.

But I don't think this should be a concern, really!

Obviously - since my start point is the same! I guess the above idea won't work even if you have the "random" increasing number based on date and time used in start_value or increment of the sequence - The clash can not be avoided for unless you do something like what Tom
suggested (partitioning values with given start points
changing (not randomly) and fixed increment. But this
ain't possible in my case.

Thanx!
Menon:)

One problem we have is:
If we use sequences, when we migrate data from
one db to the other instance of db (during product
upgrades) say, sequences result in clashes.
Since here the number of sites is not known -
we can not use the "n site" solution you
prescribe above. Have you ever run into this
situation and if so, how did you resolve it?

Thanx!

I was aware of the guid solution - wanted to see if
there is a sequence based alternative.

Thanx!!!

Tom

1.Is there any downside to having a varchar2 column as the primary key of a table.

2.Is there a downside to having a fat primary key, i.e. if a primary key is composed of 3 columns

1.col1 varchar2(20)
2.col2 number(10)
3.col3 number(10)

We are having to deal with dba's who believe that having a fat primary key will lead to performance problems, and in the kind of scenarios , they would suggest to have unique index on the combination of the 3 columns above, and then have a number column say col_key number as the primary key, which will be populated by a sequence.

Please help with illustrations.

1) no

2) more typing in predicates, that's about it.  realizing that the wider the key, the longer to compare two values -- but i would in most all cases ignore that.

there are performance "problems"
there are observed performance responses

this would not be a performance "problem".  

consider (benchmarking, far far too easy -- easier then "hypothesizing" for sure)

ops$tkyte@ORA920LAP> drop table t;
Table dropped.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create table t
  2  as
  3  select object_name, object_type, owner, max(object_id) object_id
  4    from all_objects
  5   group by object_name, object_type, owner;

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create unique index t1_idx on t(object_id);

Index created.

ops$tkyte@ORA920LAP> create unique index t2_idx on t(object_name, object_type, owner);

Index created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create or replace procedure p1
  2  as
  3      l_rec t%rowtype;
  4  begin
  5      for x in ( select * from t )
  6      loop
  7          select * into l_rec from t where object_id = x.object_id;
  8      end loop;
  9  end;
 10  /

Procedure created.

ops$tkyte@ORA920LAP> create or replace procedure p2
  2  as
  3      l_rec t%rowtype;
  4  begin
  5      for x in ( select * from t )
  6      loop
  7          select * into l_rec from t where object_name = x.object_name and owner = x.owner and
  8          object_type = x.object_type;
  9      end loop;
 10  end;
 11  /

Procedure created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> exec runStats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec p1

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec runStats_pkg.rs_middle

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec p2

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec runStats_pkg.rs_stop(1000)
Run1 ran in 328 hsecs
Run2 ran in 353 hsecs
run 1 ran in 92.92% of the time

Name                                  Run1        Run2        Diff
STAT...session pga memory         -131,072           0     131,072

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
315,673     315,410        -263    100.08%

PL/SQL procedure successfully completed.

<b>so, we are talking less then 3/100's of a second here..</b>

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> exec runStats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec p2

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec runStats_pkg.rs_middle

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec p1

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> exec runStats_pkg.rs_stop(1000)
Run1 ran in 363 hsecs
Run2 ran in 333 hsecs
run 1 ran in 109.01% of the time

<b>3/100ths of a second.. for over 30,000 rows.  do the math, it is neglible</b>


Name                                  Run1        Run2        Diff

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
314,060     314,149          89     99.97%

PL/SQL procedure successfully completed.


<b>but, did they think about the inserts taking longer, generating more redo, the extra space of the index, yadda, yadda, yadda</b>

you can benchmark that for them if you want but something like this should be enough. 

Hi

I have seen applications with many composite PK therefore many child tables are over-indexed. For example

table_1 has col_a, col_b and col_c as PK
table_2 has col_b, col_c, and col_e as PK
table_3 has col_b, col_c, and col_d

table_child references table_1, table_2 and table_3 as follows

col_a
col_b
col_c
col_d
col_e

to avoid locking we would index

col_a, col_b, col_c
col_b, col_c, col_e
col_b, col_c, col_d

Leads to many redundant indexes...

Is this bad database design? Or it is something which cannot be avoided?

1.
Are surrogate keys used to handle a situation where we have fat primary keys.
i.e.

in a situation where we have to have a primary key like ( col1, col2,col3), then we create another key column ( mostly populated by a sequence) which will be the unique identifier for the ( col1, col2,col3).

And secondly we would create a unique index on ( col1, col2,col3).

So we would have key surrogate key
( col1, col2,col3) - unique index.

If someone has to join to this table they will do so by joining to just key column.

2.Are surrogate keys and shadow keys one and the same?

Tom
i already have the problem of composite primary key that foreign in many others
so when need to update it i fall into cascading update problems,
i need ur opinion in my soluation
i think in add a not null unique key in the master table and then add a column in each child of that master and make then reference to that made and after that change the primary of master table to that new column and drop the fat foreign keys from all details tables

Hi

I am reading about indexes in the concepts guide, the examples there are quite simple since they are using simple single column indexes.

However, if my PK is formed by 2 or more columns such as

CASE_ID
BUSINESS_UNIT

If I have these rows

CASE_ID BUSINESS_UNIT
1 XA
2 YZ
2 YA
3 XC
4 YW
5 ZQ

in the leaf blocks how are these rows stored? 2 YZ and 2 YA?
Are the 2 columns concatenated? As 2YZ 2YA?

Hello

multi column range partitioned tables concatenate the columns to form the partition key, does it happen for range partitioned indexes too?

Hi Tom,
When I try to create an index on 33 columns of a table, I get the above error. But when I add constraint primary key on that table with same number of columns, I end up with an index having 33 columns. Is there a reason for not being able to create an index with 33 columns but getting it as a side effect of add constraint?

SQL> create table test_key (
  2  c1 varchar2(1),
  3  c2 varchar2(1),
  4  c3 varchar2(1),
  5  c4 varchar2(1),
  6  c5 varchar2(1),
  7  c6 varchar2(1),
  8  c7 varchar2(1),
  9  c8 varchar2(1),
 10  c9 varchar2(1),
 11  c10 varchar2(1),
 12  c11 varchar2(1),
 13  c12 varchar2(1),
 14  c13 varchar2(1),
 15  c14 varchar2(1),
 16  c15 varchar2(1),
 17  c16 varchar2(1),
 18  c17 varchar2(1),
 19  c18 varchar2(1),
 20  c19 varchar2(1),
 21  c20 varchar2(1),
 22  c21 varchar2(1),
 23  c22 varchar2(1),
 24  c23 varchar2(1),
 25  c24 varchar2(1),
 26  c25 varchar2(1),
 27  c26 varchar2(1),
 28  c27 varchar2(1),
 29  c28 varchar2(1),
 30  c29 varchar2(1),
 31  c30 varchar2(1),
 32  c31 varchar2(1),
 33  c32 varchar2(1),
 34  c33 varchar2(1));

Table created.

SQL> create unique index pk_test_key on test_key(
  2  c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32, c33);
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32, c33)
                                                                                                                           *
ERROR at line 2:
ORA-01793: maximum number of index columns is 32


SQL> alter table test_key add constraint pk_test_key primary key (
  2  c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23,c24,c25,c26,c27,c28,c29,c30,c31,c32, c33);

Table altered.

SQL> select index_name, count(*) from user_ind_columns where index_name = 'PK_TEST_KEY' group by index_name ;

INDEX_NAME                       COUNT(*)
------------------------------ ----------
PK_TEST_KEY                            33

1 row selected.

SQL> select index_name, column_name from user_ind_columns where index_name = 'PK_TEST_KEY' order by column_position ;

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
PK_TEST_KEY                    C1
PK_TEST_KEY                    C2
PK_TEST_KEY                    C3
PK_TEST_KEY                    C4
PK_TEST_KEY                    C5
PK_TEST_KEY                    C6
PK_TEST_KEY                    C7
PK_TEST_KEY                    C8
PK_TEST_KEY                    C9
PK_TEST_KEY                    C10
PK_TEST_KEY                    C11
PK_TEST_KEY                    C12
PK_TEST_KEY                    C13
PK_TEST_KEY                    C14
PK_TEST_KEY                    C15
PK_TEST_KEY                    C16
PK_TEST_KEY                    C17
PK_TEST_KEY                    C18
PK_TEST_KEY                    C19
PK_TEST_KEY                    C20
PK_TEST_KEY                    C21
PK_TEST_KEY                    C22
PK_TEST_KEY                    C23
PK_TEST_KEY                    C24
PK_TEST_KEY                    C25
PK_TEST_KEY                    C26
PK_TEST_KEY                    C27
PK_TEST_KEY                    C28
PK_TEST_KEY                    C29
PK_TEST_KEY                    C30
PK_TEST_KEY                    C31
PK_TEST_KEY                    C32
PK_TEST_KEY                    C33

33 rows selected.

SQL>
SQL> disconnect
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
SQL>
 

The developers want to replicate tables across to another database, but did not include a unique ID field, for me to create a Primary Key for replication, the only ID field is a Foreign Key, should I add a unique field or make a Primary Key from the Foreign Key? Am I making things difficult for myself in the future creating a constraint of a constraint?

Tom
Unfortunately no candidate key rows, not that conform to the Primary Key requirements, I was hoping to do a composite, rows are not unique and have nulls. Ok, I will create one for these tables using a trigger/sequence. I saw referece on this site earlier. Thanks for excellent and timely advice.

JK

So you are saying to replace composite primary key with surrogate key. What about my case below:

1- I am designing payroll system
2- e.g.,
pms_emp_mas (emp_no, emp_name, address, desig_code (fkey or pms_desig_mas), dept_code (fky of pms_dept_mas) etc..)
pms_desig_mas (desig_code, desig_desc)
pms_dept_mas (dept_code, dept_desc)

Now the requirement is I need to add a company_code in all tables (both master and transaction). Even designation/dept is exclusive for the company.

- If I add company_code in the dept_mas and desig_mas and made that as part of primary key,
- then when I create foreign key in the pms_emp_mas, I am struct, since I need to accomadate (desig_code+company_code) & (dept_code + company_code) - so company_code is repeating. (so I have 10 masters, so 10 company codes will be there in emp_master)

In this circumstance How do we design?

option 1- Make dept_code as primary key in dept_mas /desig_code in desig_mas ---> and have company code in these tables as ordinary column (not included in primary key) -->
so in this case, dept_code is unique & autogenerated through sequence and filtered based on company in the application. (may be companies will have skipped desig code - no problem I think)

(or)

2- Create surrogate key --> make desig_code+company_code as unique constraint. And use surrogate key of desig as foreign key in the employee master. (similrly for dept mas)

Which method is best. Please help.

yes I am wrong, You said that when it is more thatn 3 or 4 columns, go for surrogate key.

But in my case,

desig mas (desig_code (pkey), company_code (pkey), desig_desc)

dept_mas (dept_code (pkdy), company_code (pkey), dept_desc)

emp_mas
-------
company_code - pkey
emp_no pkey
desig_company_code (fkey - desigmas)
desig_code (fkey - desigmas)
dept_company_code (fkey - deptmas)
dept_code (fkey - deptmas)
emp_name

-- Here I need to repeat the company code, because it is a part of primary key in both desig and dept mas. (desig_company_code, dept_company_code)
-- I think this is unnecessary and not required.
-- to avoid what can I do.
-- So I decided to make desig_code (only) as primary key and make company code as just an non-attribute column, so that I don't need to repeat this in employee master. ( and in all other tables also i dont need to repeat, wherever Fkey is referred)

What is your openion Tom.

"There is no reason the client has to know what the real and true primary key in a physical schema is -- that is an implementation detail."

Let's take a canonical order entry system for, say, an auto supply store. Someone orders a list of parts. I create a header record with the data element unique to the order, name, address, etc. I create detail records for each part, the quantity, price, etc.

I create an arbitrary sequence based Pk to tie all the records together in this system and the shipping system et al.

So I need to give the customer a way of identifying THIS order so that he can get shipment date etc. From your declaritive above, I should not reveal my PK as the number he can refer to to uniquely identify this order. So i should create another sequence and another column and another index to give an Order#? Seems superfluous.

So I'm torn between your 'never' meaning 'it's generally not the case to reveal the real PK' and go through the work and overhead of a different number because if the column has meaning, it's possible that someone, somewhere will want to change it.

"ops$tkyte@ORA920LAP> exec runStats_pkg.rs_stop(1000)
Run1 ran in 363 hsecs
Run2 ran in 333 hsecs
run 1 ran in 109.01% of the time

3/100ths of a second.. for over 30,000 rows. do the math, it is neglible"

Not being nitpicky - I really want to clarify. Should this be 30/100ths of a second? (363 - 333 = 30)

Hello Tom,

I have a table containing user data like username, date of creation etc. Actually, username is unique identifier and I've decided to use it as a primary key. Username is VARCHAR2(30). But my peers told me that it was not a good idea and I should have used an artificial PK like ID of NUMBER datatype filled by a sequence. They didn't give me any reasons except that 'PK should be a number' and some notes about space usage. What is your opinion on that? Is there any reasons to have PK of NUMBER datatype?

P.S. The table is referenced by the only one FK.

Thanks in advance

please define "something subject to change"

a name is "subject to change"
a username is very "subject to change"

the only thing not "subject to change" is
something happened in the past

I don't know about using username as a primary key value. There are a number of circumstances when people want them to change ...

i) to match a new corporate standard
ii) to match a name change
iii) to match a change in application name

I think that we're all used to immutable usernames simply because that is what we always have to use, but there's a reasonable case for being able to change them.

If you had a bunch of code that referenced a username then that would obviously mitigate against actually changing it, but it would be good to have the facility anyway.

Yes of course, but I was highlighting some actual reasons why there might be cause for usernames to change, reasons that Serge might usefully consider. As Oracle practitioners I think we might be influenced by the immutability of Oracle usernames and extrapolate that into our own designs erroneously.

>> arg...

Heh heh heh.

David,

You itching for that thread on dizwell to open up again or something!?!?

I've always viewed changing a user name as something like an index "update". Create the new one, delete the old one. Not an actual in-place change and, given that, a user name is immutable in my eyes.

Now I have run into something of a less than ideal situation recently. I have a nice little schema that uses natural keys and have a mod_plsql application that runs against that schema. When I tried to "migrate" the application to html db I was a bit disappointed that html db does not seem to like composite keys with more than 2 columns. Some of my tables have 3 column natural keys. bleh.

- Mark

In my personal experience, natural keys, most of the time, turned one time or another into a subject to change, the simplest reason beeing typing errors.

Therefore: never ever use PKs that can be created/changed through "normal" user operation (forms, etc.) - the implications (problems) in complex data models far outweigh their advantages, in my opinion.

C.

>> You itching for that thread on dizwell to open up again or something!?!?

You're a very bad person, Mark Williams, verrry verrry bad :D

>> I've always viewed changing a user name as something like an index "update". Create the new one, delete the old one. Not an actual in-place change and, given that, a user name is immutable in my eyes.

Often that is forced more by system limitations anyway, right?

That was partly my point before -- that our prior experience with the limitations of other systems can lead us to inherit those limitations in our own. Obviously to change a username in Oracle is no trivial matter when objects owned by that username/schema are referenced by PL/SQL or SQL code, because the change is very difficult to cascade. It would take a very different coding regime to allow that.

However for usernames _within_ the application (ie. the people sitting in front of the tube entering data and generating reports etc) the situation is more conventional. You're likely to store their names against audit records, maybe in a table of roles and permissions, and in that case username changes are more logically feasible (although more practically feasible only if you have hand-rolled your own user/role/permission storage and logic).

There's nothing special about them at all, not in the sense that the username of an object owner in Oracle is special. They're just regular items of data.

>> You're a very bad person, Mark Williams, verrry verrry bad :D

Most likely due to my unreassuringly utter beardlessness I would have to say ;)

I did find it interesting that in the new Jonathan Lewis book he makes a reference to a system being "cursed" (his word) by surrogate keys. Of course I don't have the book with me right now, so I can't point to the page/context.

Anyway, I think this is one of those topics destined for eternal questioning.

- Mark

As a follow-up to my previous comment, it is page 271, section "Join Cardinality for Real SQL", where Jonathan states:

"Unless your system has been cursed with single-column synthetic keys all over the place, you will probably write some SQL that involves two, or more, columns being used in the join condition between two tables."
(Cost Based Oracle Fundamentals, Jonathan Lewis, Apress, 2005)

Anyway, I thought that "cursed" was a fairly strong word so I wrote Jonathan to ask about it. He kindly responded and a synopsis of that response is:

[paraphrase on]
Using a synthetic key usually introduces an additional index/unique constraint into the system. A table typically has columns that could serve as the natural key and, therefore, have a unique constraint on them as well. This introduces additional overhead in areas like redo, undo, latching, and buffer efficiency. The optimizer may examine an additional index which also results in overhead in the shared pool, latching, and cpu consumption. This may also hide information from the optimizer if the real key is hidden behind a join on a meaningless key -- for example, a data distribution pattern in the real key can be lost to the optimizer.

Users of a system often like to query by meaningful information; and primary keys can often propagate down to child tables. This means that you may have to write SQL to join tables because the thing that the user understands is in a table that is a meaningless key away from the data the user wants to see. [this is mostly a direct quote]
[paraphrase off]

Anyway, there you have it, and I hope that my paraphrase did not alter any of Jonathan's intended meaning.

- Mark

Yes, that all sounds like very valid observations -- the requirement to maintain a unique constraint+index on the natural key in addition to a primary key constraint+index on the synthetic column, as well as an increased number of joins, are well known disadvantages to the synthetic key approach. That it also can obscure statistics is also a consequence of using synthetic keys which is why they ought to be less popular in value-distribution sensitive data warehouses -- almost certainly that is less of a problem in OLTP systems that are less sensitive to value distributions (except for batch and report queries of course).

This is balanced against the performance problem of cascading PK changes to child tables if a natural key changes (and has child tables yadda yadda).

As far as diagnosing performance problems goes, because the potential effects of synthetic keys are spread widely around an application I would suggest that they are more likely to be identified as contributing to a systemic performance problem. On the other hand, should there be a performance problem caused by the need to cascade a changed PK value then the developers already know several things:

i) that it's there.
ii) that it's very obvious and very localised.
iii) what caused it.
iv) that it was avoidable with a synthetic key.

Such situations are interesting because the performance problem of synthetic keys is "the database is slow", while the performance problem of the natural keys is "we got the design wrong". Which circumstance is more likely to lead to public (ie. outside the technical team) discussion of the nature of the problem? :D

Well, all that aside I'm not intending to pick at the raw scab of recent synthetic/natural key debates, I was just saying, "here are some thoughts on the mutability of usernames in particular, and why people think they're not mutable when in some circumstances they potentially ought to be". Honest, guv!

Great thread.
One thing though Tom (I had actually shortened "though" to "tho'" but thought I had better change it in case I got in trouble :D)
I noticed that you used a trigger to populate the surrogate PK automatically with the sequence. Is this not a bit excess to requirements. Creating a new object with dependencies that must be maintained by Oracle along with the context switches, instead of just using
insert into ... (sequence_name.nextval,....)

Thanks

Jim

gr8 enough to explain

can I enforce a primary key on a varchar2 column . Are there any disadvantages of doing so ; compared to the applying the constraint on the numeric column

Tom,
I am wondering the best use of primary keys between development and production servers. In order for application development, is it better to make sure the PKs on type tables match between development and production. Or for a condition, create a condition table, and insert the keys you need into this table and reference this table for the condition.

To give you a better idea. We have a tasktype table, and our reports change for specific task types. As many as 8 of this task types can have the same condition in the report. Our PKs are created by triggers on both databases. It seems like too much work to try and keep the PK IDs the same between development and production. My suggestion was to create a table of TaskTypeIDs and use this table for the condition, instead of referencing each one individually. Or adding a flag to the table for the condition.

CREATE TABLE "TASKTYPE" ("TASKTYPEID" NUMBER NOT NULL, "NAME"
VARCHAR2(70 byte)
PRIMARY KEY("TASKTYPEID"))


CREATE TABLE "COS" ("TASKTYPEID" NUMBER NOT NULL
CONSTRAINT "FK_COS_TO_TASKTYPE" FOREIGN KEY("TASKTYPEID") REFERENCES "TASKTYPE"("TASKTYPEID"))

Here's some more info to help you understand what I am tyring to do.

insert into tasktype(tasktypeid,name)
values(1,'Document Retrieval');

insert into tasktype(tasktypeid,name)
values(2,'COS - Short Form');

insert into tasktype(tasktypeid,name)
values(3,'COS - Foreclosure');

insert into cos(tasktypeid)
values(2);
insert into cos(tasktypeid)
values(3);


normally the tasktypeids are created via trigger. But, I wanted to simplify for my purpose.

Select count(*) from tasktype
where tasktypeid in (2,3);

or

select count(*) from tasktype
where tasktypeid in (select tasktypeid from cos);

Using the latter, would prevent having to ensure PKs between development and production are the same.

The reason it matters is for our reporting. We use Crystal Reports for our reporting purposes. Certain tasktypeids have specific parts of the reports that need to come up, while others do not. We can either hard code the reports to these IDs or create the table, and put the ones that meet the condition into the table, thus negating having to hard code the IDs.

I just wanted your opinion on the issue.

Thanks,
Scott

To report on it. Using SKeys all over the place is not always a good idea... Usually, I will do a combination where you SKEy your important business entities, but not their relationships.

In this way, you can do reporting much more simply than when all you have are SKeys and massive joins to perform to get to a parent object a few layers up. Most of my clients don't like the idea of having redundant data unless it is truly required.

Just imagine your reporting on a fact table with one table you need to grab the description with 10 joins required? So, be careful on how you use SKEYs, especially when you have to query that massive fact table for reporting purposes!!!

The only draw back is it makes your programmers job a "little" more challenging and you may larger indexes to maintain, depending on whether you can utilize compression or not. In the case of not using SKeys, compression works GREAT!!!

Food for thought,


BradW

The information is really excellent. I have a question as a followup of this:
Table A(A1 NUMBER,A2 NUMBER,A3 NUMBER)
Table B(B1 NUMBER,B2 NUMBER,B3 NUMBER)
Table A and B has a many to many relationship , so Modelled associative entity Table C in following two ways:
1) Table C(A1 NUMBER,B1 NUMBER) a composite Primary Key Index in column A1 and B1. With respective Foreign Key Constraint.
2) Table C(C1 NUMBER,A1 NUMBER,B1 NUMBER) , Where C1(Surrogate Key) is a unique number generated from a sequence and is the Primary Key for C. There is a Unique key constraint on A1,B1. And respective Foreign Key constraint. And
a) A composite B Tree index on (A1,B1)
b) Two B Tree index on column A1 and B1 separately.

Table C usage are like below:
Table C rows won't be made as foreign key to other tables. As it is the bottommost table in the model.

As per the discussion in this page it seems to me that in this scnario above option 1 should be sufficient. Can you please correct me if I am wrong?
If we consider the performance which approach is best?
1 , 2a or 2b?
Is the decision of this model will be based on the size of the table?

Thanks & Regards
-Sujit

The information here is excellent and confirms everything I believe about PKs and surrogate keys in relational databases.

...having read this thread with interest I then read up on J2EE/EJB3 and it seems there is strong pressure (or even an absolute need) to design exclusively with surrogate keys for such a framework. So external project pressures may make it impossible to put into practise Tom's good advise. Personally I can't see how such "requirements" could ever result in a performant/maintainable system and severely restricts the DB designers scope for coming up with the most suitable table design. I am not even sure if the surrogate key requirement for EJB3 is really a requirement or simply one of those "it makes it so much easier to develop" arguments.

"Sad"? - master of the understatement there! "Mad" more like. I think you meant "their" decision not "your" - I had no part in it and continue to fight to get what I see is the right decision (and if not then to wash my hands of it and walk away). Rhetorical question: Does J2EE/EJB3 really need this, sounds like it just makes it easier for a developer somewhere. But thanks to you as ever for your objective wisdom.

now I get this statement from our Java gurus:
"there is a limitation in ejb3 tha components of compound Primary keys cannot be foreign keys"!!! I need to get clarification form the guru on this but if it is true then you can see why they are so keen on surrogates - very many natural PKs would be ineligable as they comprise a parent PK plus some other data to make them uniqe. Just had to share this.

Hi Tom,

I am planning to use a surrogate key to drop a 6-field composite key. My concern is: Is there a max value for a surrogate key field?
If a system is "built to last" and my surro key is an int; will there be no way that the surrogate key approach its limit?

I'm afraid that the idea of resetting the surrogate key every year is on my head, is that a good approach (if not i don't want to do that extra resetting anyway)

thanks in advance

the max limit is


ops$tkyte@ORA10GR2> select rpad('9',28,'9') from dual;
 
RPAD('9',28,'9')
----------------------------
9999999999999999999999999999

using a sequence to populate it.  Even if you generate 1,000,000 of them per second for the next 1,000 years:

ops$tkyte@ORA10GR2> select 1000000 * 60 * 60 * 24 * 366 * 1000 from dual;
 
1000000*60*60*24*366*1000
-------------------------
               3.1622E+16


you will not run out ;) 

You are right (of course) they lie - what they meant was "we do not know how to..." Now they do know. Your final shot about why they chose EJB3 is priceless and neatly sums up the confusion of emotions in my head. Enough said on this part of the thread. You have been a great help for which I thank you.

Hi

In one of your previous answers regarding having a fat primary key would affect the performance you said that it would but can be ignored.

I am not so sure, I have been running a test just for the sake to show our outsourcers that composite PKs do have impacts on the performance when range scans is used, the test showed that

a table's PK made up of 3 columns need twice consisten gets when doing a table join

when changed to a single column PK consistent gets dropped to half

To get a 100 rows with 3 columns PK 220 consistent gets were requiered, with a single PK only 100 were needed.

Hi

I wonder why you would implement an auto increment using triggers to get the sequence number?

Can't we just do

insert......... seq.nextval?

Hi Tom

Try with range scans, in the mean while I will digg the test case I have, post it later
Cheers

Can you settle an agruement several developers are having? We are using sequences as the primary key on tables. What is the best way to populate this field?

1) Use a trigger that gets sequence.nextval?
2) Write a stored procedure that puts sequence.nextval into a variable and then use that value in the insert statement.
3) Just access sequence.nextval in the insert statement.

Thanks in advance for your opinion.

Tom,

<<
o it is easier to code :

select * from p, c where p.primary_key = c.foreign_key

then

select * from p, c where p.id1 = c.fk1 and p.id2 = c.fk2 and p.id3 = c.fk3

and the first query runs faster.
>>

We have a similar situation, but there are two natural key columns varchar(16) & varchar(2).
So by having a surrogate key (created by sequence number)...how much performance gain would I get if I join with surrogate key v/s natural key.
The data in the base table is around 20 million.

Regards,
Prasad

Hey Tom,

Have you found any pros and cons in naming a surrogate primary key "tablename_id" as opposed to just "id"? I personally opt for the former - I like to have foreign key column names match their parent's primary key name. Another guy here finds it redundant, saying that when you join you're always typing the table names, or an alias, anyway (e.g. mytable.id = othertable.id).

Any opinion on that?

Thank you,
Mark

OK, thanks Tom.

Small correction to my "e.g." above: it should read "mytable.othertable_id = othertable.id" because one of them is a FK...

SELECT * FROM user_Tab_Columns WHERE
column_name = 'MyTable_ID'


Oh yeah, can't just do that to find all the tables that share a common column.


SELECT * FROM user_Tab_Columns WHERE
column_name = 'MyTable_ID'
UNION ALL
SELECT * FROM user_Tab_Columns WHERE
table_name = 'MyTable' and
column_name = 'ID'



Who cares that it's redundant in one place. Programmers are just too hung up on Maximizing/Minimizing/Optimizing EVERYTHING.


FROM
MyTable mt,
YourTable yt,
hisTable ht,
theirTable tt
WHERE
mt.yourTable_id = yt.id and
yt.hisTable_ID = ht.id and
ht.Theirtable_id = tt.id

I just don't find that easy to quickly decipher. I spend more time bouncing around the aliases and the column names than this:

FROM
MyTable mt,
YourTable yt,
hisTable ht,
theirTable tt
WHERE
mt.yourTable_id = yt.yourTable_id and
yt.hisTable_ID = ht.hisTable_id and
ht.Theirtable_id = tt.Theirtable_id

When you see the same column name on both sides it makes reading (or more importantly debugging) the Statement so much easier.

Even if you only slightly agree with me or you think that it is easier to do it my way but just barely easier, what benefit do you get out of it the other way? Some religious adherence to divine redundancy elimination. I am a developer, I understand the personality flaws/quirks that make us see almost everything we do as an oppotunity for Maximizing/Minimizing/Optimizing. I put the cream and sugar in my coffee first so that the addition of the coffee does the stirring, I save throwing a stirrer away every cup. I mean, I get it. But Come On, give it up when doing the 'Unnecessary' thing actually makes something else easier.

Absolutely, there are opinions as to what is more or less readable.

In the case of the poster above, the recalcitrant colleague made an argument not for clarity but for redundancy elimination. My point is that if it is more readable for 1 developer and the rest are like you, see no difference, and the one roadblock is some guy whose only argument is that it is "redundant" then that's a purely religious stand by his captious peer.

As you say, and quite often, not everything is bad (triggers?). Tools have their place. Even redundancy is useful. Ask the space shuttle astronauts if they want to eliminate redundancy.

Redundant isn't a reason unto itself. It should be,

it's redundant and that makes is slower
it's redundant and that could cause an error
it's redundant and that takes a lot more space to store

Here¿s something I haven¿t seen covered yet. If it has, then accept my apologies for the wasted pixels.

Example 1:

cat
cat_id (PK)
cat_nm (AK 1)

manu
manu_id (PK)
manu_nm (AK 1)

cat_manu
cat_id (PK) (FK) -> cat.cat_id
manu_id (PK) (FK) -> manu.manu_id

item
item_id (PK)
item_nm (AK 1)
cat_id (FK) -> cat_manu.cat_id
manu_id (FK) -> cat_manu.manu_id

When a category and manufacturer combination is changed, the item table must also be modified which helps reinforce the RI since it raises awareness that if you change the relationship between categories and manufactures you change the meaning of the item.


Example 2:

cat
cat_id (PK)
cat_nm (AK 1)

manu
manu_id (PK)
manu_nm (AK 1)

cat_manu
cat_manu_id (PK)
cat_id (FK) (AK 1) -> cat.cat_id
manu_id (FK) (AK 1) -> manu.manu_id

item
item_id (PK)
item_nm (AK 1)
cat_manu_id (FK) -> cat_manu.cat_manu_id

With this example, someone can change the combination of category and manufacturer (update the cat_id or manu_id for a cat_manu_id row in the cat_manu table) which then affects the records in the item table without the programmer/user aware that they just changed the meaning of the item.

I do agree with Tom that when you get to four or more columns in a composite key it can be hard to maintain the SQL that is written, so with that in mind, I will succumb to the surrogate key. But, I still prefer to force the programmer/user to deal with the relationships explicitly via the use of composite keys whenever possible.

What say you community?

Hello Tom,

You suggested that "If the above is used as a foreign key in many other tables" then go for surrogate key. But Jonathan Lewis suggests that having composite key as primary key is useful to avoid unnecessary join. This comment can be seen at ( http://jonathanlewis.wordpress.com/2006/12/29/meaningless-keys/ ). Now I am confused. Please explain.

Also, you are always (OK most of the time!) in favour of using natural keys. What are the advantages of doing that?

Regards,

Arvind

Tom:

You say Primary key should never be updateable.

I agree but does not this mean that any data that is entered by a user should never be a PRIMARY Key since a user might make a data entry error and wants to fix it.

For example, if i receive an Invoice from company XYZ and i log it into my invoice table, I would not be able to use their invoice number because i might make a typo and want to fix it so i should use a surrogate invoice id (system generated) for PK. correct?

Tom:

My point is that all natural KEYS are part of the data entered by user (not system generated). I can't imagine a system that wont let a user fix a typo or data entry error.

Does not that mean to not use natural keys as they are most of time updateable.

Tom:

I see your point on validation tables. I meant more on transaction tables.

Still if you have screens to maintain a state lookup table and a user entered "VC" instead of "VA", then he has to go back and update the code.

Your solution is to delete the record and enter a new one and not update. right!

Hi Tom,

I am designing data tables for a web based SAAS application. All the tables are going to have a primary key column say (col_pk) and a tenant_id column. All though col_pk is primary key but i want to create a composite primary of col_pk and tenant_id as almost of select on all table will have at least one where condn like "select * from mytable where col_pk = some_value and tenent_id = other_value".

This would prevent some other tenent to see the pk data of some other tenent apart form other security.

Please suggest.

Thanks

Vikas Sharma

ops$tkyte%ORA10GR2> create table t
  2  ( x int constraint t_pk primary key using index( create index t_pk on t(x,y) ),
  3    y int,
  4    z int
  5  )
  6  /

Table created.

Tom, do you know whether the order of the columns in the composite primary key declaration is important? I am using Oracle 10g.

For instance, in a table with two columns
does CONSTRAINT "MY_PK" PRIMARY KEY ("COL1", "COL2") mean exactly the same thing as CONSTRAINT "MY_PK" PRIMARY KEY ("COL2","COL1")?

Hi Tom,

I have a question. Here is the data structure.
--Invoice Header Data
CREATE TABLE sujith_invoice_header_tab(invoice_id number, invoice_date date, invoice_amount number);

invoice_id is the PK

--Invoice Lines
CREATE TABLE sujith_invoice_item_tab(invoice_id number, item_id number, line_code VARCHAR2(20), line_amount NUMBER);

Invoice_id and item_id makes up the PK, invoice id is the FK to header

CREATE TABLE sujith_invoice_line_tax_tab(invoice_id number, item_id number, tax_code VARCHAR2(20), tax_amount NUMBER);

Invoice_id, item_id and tax_code is the PK and there are two FK to header and item.

What if someone argue that you are repeating same data (invoice_id and line_item) and that instead you should use unique system generated(sequence + trigger) id's for line item and tax tables?

What are the pros and cons of both approaches and which one would you recommend??

Thanks,

Sujith

Hi Tom,
After posting the above question I kept googling and found the answers I was looking for is under "Natural Key Vs. Surrogate" topic.

Is there a Oracle specific view/points and recommendations under that topic??

Thanks,

Sujith

What are the pros and cons of using the primary key of the parent table as part of the primary key in the child tables versus just having it as a foreign key in the child tables?

Please shed some light on this.

Hi Tom,

Thanks for your response.Are there any performance issue, if we use the foreign key as part of the primary key?

Hi Tom,

Can you please explain what you are trying to convey in the below line with an example?
"You would be hard pressed to find a case against doing it"

Are there any performance issue, if we keep the foreign key as part of the primary key?

Thanks for your help.

Regards,
Chella

Hi Tom,
reading the first post, I understand you was in favor of using a surrogate key, only is some particular circumstances and mostly because of:
A) the space savings (when the key columns are more than 3 and referenced in many tables)
B) the update cascade (when could happen) is avoided, update is limited to one row in one table
C) coding requires less effort (only one column to join)
Going on with reading, I was not able to find any answer supporting/suggesting an advantage in terms of performances using surrogate instead of natural keys. At the contrary, I read many other reasons for not using surrogates keys. Would you change/add anything in your answer on this subject after many years and in relation with Oracle 11gR2?

Hi Tom,
I'm in a problem again :(
Currently I'm working with a company that bought it's ERP application from a Vendor (actually bad software vendor).
The "Main Master Table" has a composite primary key with 8 columns !!! and there are more than 10 detailed tables for this table.
My duty is to generate/extract a management reports, while doing that if need to join 3 tables i have to write 16 join condition, of course if i need to join 8 tables it's 56 join conditions !!!!
My Question: I want to build a new schema to simplify the structure by adding a new column "ID" instead of the columns primary key/foreign keys, how can implement it???

My thoughts:
- create new user
- create a sequence
- create a mapping_table with 9 columns
1 -> new primary key "ID" (using the sequence)
[2-9] -> the 8 old composite primary key
- create trigger on the Master Table After I/U/D to I/U/D on the mapping table
- create a Materialized view for the Master Table (join between Old Master and the mapping_table "to get the ID")
- create a Materialized views for all detailed Tables
after doing that, can i create indexes on materialized views???