Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: primary and foreign keys

Re: primary and foreign keys

From: <nasof_at_my-dejanews.com>
Date: Thu, 30 Jul 1998 13:22:48 GMT
Message-ID: <6pps38$pce$1@nnrp1.dejanews.com>


TRY: create sequence my_seq_num;
create table my_table (id1 number, text varchar(10)); insert into my_table values ( my_seq_num.nextval, 'My text');

This gives you 38 digits of uniqueness. That's a max of 99,999,999,999,999,999,999,999,999,999,999,999,999. That should hold you for a while...

BUT..... Let me say this about going away from "meaningful keys":

First, you reduce joins: You can select rows from a child table where the parent key (or part of parent key) is "blah...". Otherwise you have to join to the parent table and query on the key or part of key (in which case you still want an index!

Second, if the data you want is in a foreign key or primary key in the table, then you can get all the data through a single index lookup.

Third, you reduce confusion. Using meaningful keys makes queries a bit easier, you join on what makes sense, not some meaningless id. Development and maintenance are ensured because you maintain the ref. integrity of the db based on the data, not a unique key. You can look at just the child table and gleen an understanding of the data because it has meaningful data about its parent.

Hope this helps...

-Frank

In article <35BFD48E.10B1D83D_at_dsto.defence.gov.au>,   Ronnie Braverman <Ronnie.Braverman_at_dsto.defence.gov.au> wrote:
> Hi there.
> I have table X with the primary key PK_X consisting of five
> columns.
> I have table Y with a foreign key FK_Y the same columns as
> those in PK_X.
> Since the number of rows in Y is going to be huge, I'd like
> to avoid duplicating the key columns in both tables X and Y.
> What I would like to have is some unique id (eg a unique
> number) being generated for every row in table X which I could
> then use as the primary key for X and the foreign key for Y.
> The problem is that I do not know how to generate a unique
> id (other than the [maximum of the column] + 1, but since the
> number of rows are going to be huge and rows will be added and
> deleted continuously, this is not a good long term solution).
> Any ideas?
> Thanks,
> ronnie Braverman
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Jul 30 1998 - 08:22:48 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US