deptno is different.because it is meanful.
if user always know deptno , then they can go directly to
emp table and do the query, but if they only know deptname,
they will need to go through dept table.

sometimes we may add some meaningless surrogate key and user don't know it actually. so they need to go through some other table to get the key first, so it take one more join.

when i post my question at dbasupport, somebody tell me that
they want to use surrogate key as much as possible.
what's your opinion?

If we are using surrogate keys, then we will have to use an unique index on columns which would have made up the natural key of the table.

Is the above statement true.

For example, in the department table if you use a surrrogate key dept_id, then you have to use a unique index on deptno, as there cannot be two departments with the same deptno.

Thank you

Tom please distinguish between

1.Unique Constraint Vs Unique index

"they are different. constraints are what you use to enforce uniqueness -- and
they may use a unique or non-unique index as appropriate to help them do their
job.
"

2.How can we enforce a unique constraint using a non unique index. Please explain.

<Tom>
create table t ( x int unique DEFERRABLE );
you'll find we create a unique index by default there.
</Tom>
… you have it correct in other threads ... non-unique.

<Tom>
and all it needs is an index -- any index.
</Tom>
… any B*Tree index … bitmap won’t do for sure :)

Sorry man, I know this was a low blow … only wanted to prove context is important … since on this case …

<A reader>
If we are using surrogate keys, then we will have to use an unique index on columns which would have made up the natural key of the table.
</A Reader>
… you said …
<Tom>
the above statement is false.
the correct statement is "we will have to use a unique CONSTRAINT on the columns that would have made up the natural key"
</Tom>

… without really saying why is it false (the fact you claim yours is true doesn’t necessarily make the other false).

Let us ignore the “have to” part, which would make both statements false (nobody is twisting arms … although, personally I would recommended it … if a natural key is not enforced, preferably with an unique constraint), and replace it with “could”.

So what was the context? … the problem being addressed? Well, my reading of the thread is something like “An Entity has a natural key (hence ensuring uniqueness is important) … when implementing the table out of this entity we decide to have a surrogate primary key … what is to happen with the natural key?” … and not “what is the best way to go about implementing the natural key”! The better way? … yes, the unique constraint! But surely I can create an unique index on a column without having declared an unique constraint on it … and uniqueness is safeguarded! The best way? … imho, unique constraint + mandatory column(s) … I’ll state my case if you think is necessary.

In fact, you “provoked” the reaction by stating, right at the beginning:
<Tom>
A surrogate key is simply a single column replacement for the NATURAL key of the table.
</Tom>
… “replacement” wasn’t the best choice, was it? … “in addition to” or anything that doesn’t suggest an abandonment of the natural key would’ve been better.


Since I’m here … I have a real question (not just opinions :)) ..

SQL*Plus: Release 9.2.0.1.0 - Production on Fri Sep 26 23:09:32 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> create table a ( x int primary key );

Table created.

SQL> select constraint_name, constraint_type,index_name from user_constraints where table_name='A';

CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
SYS_C003040                    P SYS_C003040

SQL> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 X                                         NOT NULL NUMBER(38)

Why is not the NOT NULL constraint showing in USER_CONSTRAINTS? Where is DESCRIBE getting its info from?

Gabriel
 

The other issue aside, I was hoping you would have a word or two on ...

create table a ( x int primary key );

... after which DESCRIBE shows a NOT NULL check constraint on X but USER_CONSTRAINTS doesn't.

Gabriel

No question ... the primary key column is/has to be constrained against NULL values ...

Concepts, Chapter 21, Section: Primary Key Constraints and Indexes
<quote>
Oracle enforces all PRIMARY KEY constraints using indexes. In Figure 21-5, the primary key constraint created for the department_id column is enforced by the implicit creation of:
A unique index on that column
A NOT NULL constraint for that column
</quote>

if ... <Tom>the primary key is the NOT NULL constraint</Tom> then I should be able to re-state the above as "... the primary key constraint ... is enforced by the implicit creation of a unique index and a primary key constraint ..." and expect it to be correct.

To me, that <Concepts> statement is misleading ... but, yes, it may be my problem after all.

Disclaimer: never doubted the value of the advice you provided in this thread, just the way you sometimes did it (as in "choice of words").

In the grand scheme of things, all these semantics are not worth 1 penny ... and so I'll stop before being told "get off my site you vagabond".

Gabriel

Tom,

Following …

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:12678058160686, <code>

I decided to insist in arguing …
<statement to be proven>
In Oracle, one could implement the natural key of an entity with
A. an unique index
B. an unique constraint
are either both TRUE of both FALSE.
</statement to be proven>

I’ll only do the “both FALSE” part to keep it short.

If you argue the above statement does not reflect what has been stated so far in this thread then I’ll agree to disagree (since we are totally disconnected) and call it quits.

You valuated A as being FALSE and B as being TRUE … and the only obvious objective criteria you could have used is B is “more complete” than A.

If you disagree with this “criteria” then let me know what was your criteria … if it is “because I said so” or “Oracle says so” than I’ll call it quits.

Based on this valuation system I could come up with option C and argue C is the “most complete” … hence B is FALSE; or I could prove B cannot be TRUE (without having to even state C … I can show there has to be something more than B states).

When it comes to ER modeling, Oracle provides plenty of features (it is an RDBMS after all) … for the “primary key” ER concept, Oracle provides the “primary key constraint” construct … for the “natural key” ER concept, Oracle does not provide a construct (I’m not aware of any “natural/candidate/alternate key constraint” … and, as I am out to prove, the “unique constraint” construct is not it either).

So, what is the difference between the “primary key” and “natural key” ER concepts? … none whatsoever except for an arbitrary choice of which one should be the “primary” … that is the one that usually gets migrated to related entities (in theory, both can be migrated to different dependent entities). The “primary” and “natural” words are really used to provide nuances to essentially the same thing … a “key of an Entity” … which specifies that each instance of the Entity can be uniquely identified by the values in the key. In fact, they don’t talk about “natural keys”, but “candidate keys” which, after the selection process, become “primary” and “alternate key” … but we can stick with the “natural”.

Let us assume, we have an entity E=(x1,x2,x3,x4,…) with both K1=(x1,x2) and K2=(x3,x4) as ER keys … they are also “natural” because we didn’t artificially create X1, X2, X3, X4 or derived them from other attributes … they are all natural attribute of the entity E. Is it conceivable such entity exists in real life? I would say, Yes.

Having identified K1 and K2 let me decide on the “primary” … well the candidate keys have already been established … the only thing I’m doing now is toss a coin … the decision of which one is “primary” doesn’t have anything to do with the existence or fabric of the keys. Let us assume we pick K2 as the “primary”.

We implement E as Oracle table T, K1 as an Oracle unique constraint and K2 as an Oracle primary key constraint.

… insert into T (x1,x2,x3,x4,…) values (NULL,v2,v3,v4,…) … where v3, v4, v5 are NOT NULL.

Question: can I now reverse my toss of coin and pick K1 as the “primary”? … NO … a totally arbitrary event has introduced differences between the candidate keys.

Of course, that NULL into x1 did it … which goes to prove, when implementing a candidate/natural key, one also has to say something about the nullability of the attributes making up the key. The “unique constraint” Oracle construct, as opposed to the “primary key” one … does not make any provisions regarding the nullability of the columns included in its definition. Hence there has to be more than what B states in order to be complete. Hence, based on the “completeness” valuation system, B is FALSE … in fact, both A and B are FALSE.

End.

If you find flaws in my little Boolean excursion please point them out. If you think all of this has no value whatsoever then yes, let us call it quits.

Opinion coming …

So what should that option C be in order to evaluate to TRUE? Well, in Oracle, in order to preserve this little bit of logic above (and if no flaws in it, of course), one should implement a candidate key of an entity with an Oracle unique constraint and NOT NULL constraints for all component columns … or as a primary key constraint of course (but only one such PK constraint can exist for a table).

I find this a bit strong since the only problem posed by NULLs when implementing a “key of an Entity” is NULL-tuples. All values of type (v1,v2), (v1,NULL), (NULL,v2) can uniquely identify each instance of an Entity …. the only problem is the (NULL,NULL) pair. One could then argue, that including the NOT NOLL constraints in the Oracle primary key construct is too strong.

But this is an opinion … things are and will stay the way they are. And that’s perfectly OK with me. Practicality always wins.

Gabriel

i have been given a conceptual data model containing three entities A, B, and C. in this model A is "master". an instance of B is dependent upon the existence of an instance of A. C is dependent on B in the same way B is on A.

this leeds to a relational model consisting of three tables A, B, and C. B holds A's primary key as non-nullable foreign key. C gets B's primary key value as a non-nullable foreign key as well. foreign keys are not parts of primary keys in B and C.

question is how to maintain an additional requirement postulated by client developers: they want A's primary key value to be included in C in order to simplify client side code and increase query performance (no join needed for A's primary key value in queries on C).

in other words: they want to "short circuit" navigation by modelling a redundant relationship A-C that is equivalent in meaning (redundant) to relationship A-B-C.

but establishing such an RI-constraint between A and C wouldn't suffice at all since this doesn't prevent invalid foreign key values for A in C (not matching the associations between A, B, and C):

A -- 1 --> B -- 2 --> C
A -------- ? -------> C

here A's primary key value of 1 gets migrated as foreign key to B. B's primary key value of 2 in turn is propagated to C, so 1 would be the only correct value for A's primary key value as foreign key within C. but this can't be enforced by the abovementioned relationship A-C ...

since i would suggest to introduce surrogate keys (generated by sequences) instead of the natural keys (that would became alternate keys) anyway, an additional foreign key for A in C wouldn't make sense anymore.

but is there a non-procedural (model driven) solution to keep both such relationships "in sync" at all?

well, one way to do this is to change what C references in B. 


ops$tkyte@ORA9IR2> create table a ( a int constraint a_pk primary key );
Table created.
 
ops$tkyte@ORA9IR2> create table b ( a references a,  b int );
Table created.
 
ops$tkyte@ORA9IR2> create index b_a_idx on b(b,a);
Index created.
 <b>
ops$tkyte@ORA9IR2> alter table b add constraint b_a_unique unique(b,a);
Table altered.
 </b>
ops$tkyte@ORA9IR2> alter table b add constraint b_pk primary key(b);
Table altered.
 
<b>both of those constraints will use the index on (b,a) we've created</b>

ops$tkyte@ORA9IR2> create table c ( a int, b int, c int constraint c_pk primary key);
Table created.
 <b>
ops$tkyte@ORA9IR2> alter table c add constraint c_fk foreign key(b,a) references b(b,a);
 
Table altered.
 </b>

ops$tkyte@ORA9IR2> @dbls
 
OBJECT_TYPE  OBJECT_NAME                    S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX        A_PK                             USERS
             B_A_IDX                          USERS
             C_PK                             USERS
 
TABLE        A                                USERS
             B                                USERS
             C                                USERS
 
 
6 rows selected.


<b>so, you can only have C point to valid B,A combinations -- fixing the issue with C pointing directly to A</b>


Not 'pretty', but it works -- and data integrity is ensured..



 

although there 's at least no trigger needed for RI. but on the other hand B's "primary key" wouldn't be the minimal unique identfiying set of columns anymore. so one would have to add an appropriate non-composite alternate key ...

i think i'll try to force my surrogate key approach.

Tom,

In this thread, you said...

> Even in tables that appear to have a natural key, I use surrogates. This is to
> protect myself when people decide the primary key value needs to be changed (and
> they always do). The only imutable column in the table is really the surrogate
> key -- the rest are subject to modification. Since I don't want to have to
> cascade that update to dozens of tables -- i use the surrogate.

What would you suggest for a table partitioned and archived by date? I was told to avoid using global indexes, so the only solution I could think of is to prefix the artificial PK with 'YYYYMMDD' so that partitioning by the PK would be equivalent to partitioning by date. But how could I enforce that the prefix match the date? Is there a better approach?

<quote>
<quote>
I was told
to avoid using global indexes
</quote>

why? global indexes are generally going to be a fact of life in many cases....
</quote>

I don't know. My boss told me it was bad practice. Does maintaining them add a lot of time during partition operations? I certainly agree with your advantages to having them.

I blamed the wrong guy. My boss got that mandate from the DBA group. The DBAs don't like global indexes because they require downtime for the application during partition operations. Keep in mind our production database is still running on 8i.

I was also told by a DBA that query performance will be about the same between using local indexes and global indexes, though I've seen others express a differing view (including you). Guess it's time for that benchmarking thing again.

Well the argument was that it would not make much of a difference because looking through one big b-tree index wouldn't be much faster than looking through several small ones.

Of course, we could partition the global indices too, can't we? Would that have any additional cost in partition operations compared to unpartitioned global indices?

<quote>Yes, it adds time during the partition operations -- but so? People can still
query the data whilst this is happening and they get the answers rapidly.
Without totally slamming the system day to day.</quote>

I finally got some time to experiment with partitioning (I'm not a DBA). I found that during partitioning operations, local indexes on the partitions affected become unusable, so the added time for partition operations does indeed make a difference. Is there a way to UPDATE LOCAL INDEXES too?

Tom said :
"I mean, say by MAGIC the height of the index went from 4 (one big one) down to 2 (that would be unusual) and say you did 16 partitions.

Ok, well, the global index does 4 IO's to find a key.

the local -- the 16 you have to scan -- must to 16*2 IOs = 32 IOs."

Should not database only search that particular INDEX PARTITION for the data, if its a range scan (say, partition by date)?

What am I missing here?

Hi,

I'm putting this question as part of the first question which is raised in the thread. If we have tables like
a) T1 (a int primary key)
b) T2 (b int primary key)
c) T1T2 (surr_key primary key, a refers to T1, b refers to T2)
d) T1T2_details (surr_details_key primary key, surr_key refers to T1T2, some additional specific columns)

Now if you have a query which needs the specific columns from T1T2_details based on the search keys (a) & (b), you will need to join all of them to get this info. However, if you have used natural keys you don't need that join the single table (T1T2_details) should suffice!

Am i correct?

What would you recommend if the natural keys will change here, is there any alternative where i can get best of the both worlds. Like reduce the number of joins and still use surrogate keys?

Thanks
Pranav.

Just modifying the table here -
T1 (a int primary key, a_naturalkey int); -- a is the surrogate key
T2 (b int primary key, b_naturalkey int); -- b is the surrogate key

And
T1, T2 are many-many relationship (so you have T1T2 as a bridge table)
T1T2_details (is child of T1T2)

Thanks
Pranav.