| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: DB Design question: Do you create a separate PK in the join table?
This is in fact incorrect. Replication DOES work on tables without primary
keys, you just need to tell Oracle what would be the columns to treat as
though they were primary keys. From the 8i replication API manual:
SET_COLUMNS procedure
To use an alternate column or group of columns, instead of the primary key, to determine which columns of a table to compare when using row-level replication. You must call this procedure from the master definition site.
See "Using Multimaster Replication" in the Oracle8i Replication manual
Syntax
DBMS_REPCAT.SET_COLUMNS (
sname IN VARCHAR2, oname IN VARCHAR2,
Note:
This procedure is overloaded. The column_list and column_table parameters are mutually
exclusive.
HTH. Pete
Andreas Michler wrote:
> Yes your are right.
> Every table in every database should have 1 primary key.
> I.e. an replication under oracle does not work correcty on tables without
> pks.
>
> gmei_at_my-deja.com wrote:
>
> > Hi:
> >
> > We are designing a new oracle database for shop. We just had a long,
> > heated debate about whether we should create an independent Primary key
> > in join tables. Fox example:
> > We have three tables: UserTable, AccountTable, UserAccountTable. And we
> > want to support that a user can have several account and an account can
> > be owned by several users (join account).
> >
> > UserTable:
> > User_ID (PK)
> > User_FirstName
> > User_LastName
> > ....
> >
> > AccountTable:
> > Account_ID (PK)
> > Account_Name
> > ...
> >
> > As far as UserAccountTable goes, there are two options:
> >
> > 1. UserAccountTable:
> > User_ID (FK)
> > Account_ID (FK)
> >
> > The primary key in this table is the composite key which is made of
> > both User_ID and Account_ID.
> >
> > 2. UserAccountTable:
> > UserAccountID (PK)
> > User_ID (FK)
> > Account_ID (FK)
> >
> > Here you still have FK constraints, but you add one independent PK.
> >
> > I am for option 2. I think every table should have it's own independent
> > PK. The systems I designed and worked before always do that. It just
> > makes the DB admin work easier later (in case you might want to do
> > batch updates in the join table). But the frond end programmers are
> > against this, saying it creates extra complexity, and it will confuse
> > them when they do java code.
> >
> > I am a very open minded person and I am asking you, the experience db
> > guru, to give your opinion of what is the "better" option. What are
> > the potential problems later in each approach?
> >
> > The thing I am afraid of is that we made the decision now and 6 months
> > later we may need to change it. That will cause lots of problem because
> > there are too many places to change. I would rather to make
> > the "better" (or "correct") decision now and live with that.
> >
> > Thanks for your time and you help is appreciated.
> >
> > If you reply, please send a copy to zlmei_at_hotmail.com
> >
> > Guang
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
> --
> -------------------------------------
> ADICOM Informatik GmbH
> Andreas Michler
> Wiesfleckenstr. 34
> 72336 Balingen
> Tel: 07433/9977-57,Fax: -90
> E-Mail: Andreas.Michler_at_adicom.de
> http:\\www.adicom.de
> -------------------------------------
Received on Thu Jan 13 2000 - 07:43:56 CST
 |
 |