473,549 Members | 2,753 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multi-Duplicates OK-Primary Keys

Hey all,

(Access 2000)
I've been having a horror story with this design problem. My Database is
Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each
Item in each table needs a unique ID# based on its context.

Primary Keys
AUTHORS = AuthorID - NO Duplicates

BOOKS = AuthorID - Dups OK
BookID - Dups OK

PAGES = AuthorID - Dups OK
BookID - Dups OK
PageNumber - Dups OK

So for instance the pages need to number from 1 to <i>n</i> for each Book of
each Author.

The problem I had after I set this up is that from PAGES to BOOKS
I could NOT use referencial integrity because it could not identify a unique
Primary Key, (because I used Duplicates OK = True in BOOKS)

-> Is this not a valid way link a 3 level database?

I tried using auto-number primary keys but that killed the validation for
duplicates in the table.

-> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True the
same as one primary Key with Dups = NO so that I can set up integrity
between tables using these two primary keys?

Thanks for any insight!
~Philip
Nov 12 '05 #1
7 5338
Philip wrote:
-> Is this not a valid way link a 3 level database?
Oh yes it is.
I tried using auto-number primary keys but that killed the validation for
duplicates in the table.
A *very good* observation. Write it down for future reference. :-)
-> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True the
same as one primary Key with Dups = NO so that I can set up integrity
between tables using these two primary keys?


Nah. You have *one* primary key, which may consist of more fields, each
with its own index (duplicate or not) but the composite *MUST BE UNIQUE*
otherwise it doesn't work as primary key.

In the design view of the tables, select all rows pertaining to the key,
and only then press the 'key' button to set those as primary key.

--
Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Nov 12 '05 #2
"Philip" <Ph****@afterap riltax.com> wrote in message
news:Wq******** ***********@nwr ddc01.gnilink.n et...
Hey all,

(Access 2000)
I've been having a horror story with this design problem. My Database is
Structured like This: AUTHORS, BOOKS, PAGES. Those are the Tables and each
Item in each table needs a unique ID# based on its context.

Primary Keys
AUTHORS = AuthorID - NO Duplicates

BOOKS = AuthorID - Dups OK
BookID - Dups OK

PAGES = AuthorID - Dups OK
BookID - Dups OK
PageNumber - Dups OK

So for instance the pages need to number from 1 to <i>n</i> for each Book of each Author.

The problem I had after I set this up is that from PAGES to BOOKS
I could NOT use referencial integrity because it could not identify a unique Primary Key, (because I used Duplicates OK = True in BOOKS)

-> Is this not a valid way link a 3 level database?

I tried using auto-number primary keys but that killed the validation for
duplicates in the table.

-> Shouldn't Access treat two(or more) Primary Keys with Dups OK = True the same as one primary Key with Dups = NO so that I can set up integrity
between tables using these two primary keys?

Thanks for any insight!
~Philip

Do you realise the difference between a primary key and a unique index? You
can have a primary key PageID which is simply an autonumber to identify the
record, yet still enforce a unique index so that you cannot have a duplicate
AuthorID, BookID, PageNumber.
While looking in the table in design view, select View>Indexes and create a
new one called idxPages then select these 3 fields, one below the other, and
set primary to 'no' but unique to 'yes'.

Will this help you do what you want?

Fletcher
Nov 12 '05 #3
Thank you for the swift response!
I was able to get the relationship working for AUTHOR-BOOK-PAGE with just a
few tweaks
Nov 12 '05 #4
> Do you realise the difference between a primary key and a unique index?
You
can have a primary key PageID which is simply an autonumber to identify the record, yet still enforce a unique index so that you cannot have a duplicate AuthorID, BookID, PageNumber.
While looking in the table in design view, select View>Indexes and create a new one called idxPages then select these 3 fields, one below the other, and set primary to 'no' but unique to 'yes'.

Will this help you do what you want?


Thank you for the explanation, another small piece of my ignorance has died
at your hands, however,
I ran into an issue with the second part of the DB and your explation seemed
like it would provide the solution, but I cannot get it to work the way you
described...

The following relationship describes the same AUTHOR DB with two different
Tables

Primary Keys
AUTHORS = AuthorID - DupNO

BOOKCATEGORYS = AuthorID - DupOK
BookCatId - DupOK
BookCatOrder - DupOK

BOOKORDER = AuthorID - DupOK
BookCatId - DupOK
BookOrder - DupOK

Each Author has different categorys for his/her books, each category has a
certain list of books and an order for them. Note: The Categories also have
an BookCatOrder ID

I tried your suggestion and Made The first two Fields (AuthorID and
BookCatID) the only primary's
while I made a multi field index for the two Tables with all three fields,
so those fields would be unique.

This allowed me to create a relationship with integrity but did NOT validate
uniqueness!

I was still able to enter data like this for BOOKCATEGORYS
AuthorId | CatId | BookCatOrder
1 | 1 | 2
1 | 2 | 2

Two different Categories are claiming to be the second in the Category Order
It was only validating the primary keys for uniqueness

-> Are there limitations when using index's with the primary keys on same
fields? I have never used multi-field indexes.

Thanks,
~Philip
Nov 12 '05 #5
"Philip" <Ph****@afterap riltax.com> wrote in message
news:IY******** ***********@nwr ddc02.gnilink.n et...
Do you realise the difference between a primary key and a unique index? You
can have a primary key PageID which is simply an autonumber to identify

the
record, yet still enforce a unique index so that you cannot have a

duplicate
AuthorID, BookID, PageNumber.
While looking in the table in design view, select View>Indexes and

create a
new one called idxPages then select these 3 fields, one below the other, and
set primary to 'no' but unique to 'yes'.

Will this help you do what you want?


Thank you for the explanation, another small piece of my ignorance has

died at your hands, however,
I ran into an issue with the second part of the DB and your explation seemed like it would provide the solution, but I cannot get it to work the way you described...

The following relationship describes the same AUTHOR DB with two different
Tables

Primary Keys
AUTHORS = AuthorID - DupNO

BOOKCATEGORYS = AuthorID - DupOK
BookCatId - DupOK
BookCatOrder - DupOK

BOOKORDER = AuthorID - DupOK
BookCatId - DupOK
BookOrder - DupOK

Each Author has different categorys for his/her books, each category has a certain list of books and an order for them. Note: The Categories also have an BookCatOrder ID

I tried your suggestion and Made The first two Fields (AuthorID and
BookCatID) the only primary's
while I made a multi field index for the two Tables with all three fields,
so those fields would be unique.

This allowed me to create a relationship with integrity but did NOT validate uniqueness!

I was still able to enter data like this for BOOKCATEGORYS
AuthorId | CatId | BookCatOrder
1 | 1 | 2
1 | 2 | 2

Two different Categories are claiming to be the second in the Category Order It was only validating the primary keys for uniqueness

-> Are there limitations when using index's with the primary keys on same
fields? I have never used multi-field indexes.

Thanks,
~Philip

Hi Philip
I did take a second read through and still didn't get it - sorry. I think
the problem is I can't quite understand the table structure (I'm not even
100% sure whether the word 'order' means 'position in a list' or 'order as
in a sales / purchase order'). Perhaps it's too late at night or perhaps
someone else can help out here, but while an AUTHORS table seems straight
forward, I can't see the sense of the structure for BOOKCATEGORYS.

Anyway, my general point was that you could have a single autonumber for
each table as the primary key. This allows you to refer to this record in
another table. If you want to keep a value, or combination of values
unique, then you can use additional indexes. For example,
AuthorId | CatId | BookCatOrder
1 | 1 | 2
1 | 2 | 2


Was your objection that BookCatOrder=2 should occur no more than once in the
table? If you need this to be the case, then have a single index that says
that BookCatOrder must be unique. Your table can contain a number of
indexes - each involving single of multiple fields and each with the ability
to be unique. Obviously only one can be the primary key, but this does not
(and perhaps should not) contain any meaningful data (ie arbitrarily
assigned autonumber).
Fletcher




Nov 12 '05 #6
Fletcher,

I apologize because it DID work exactly as you explained! I think I've been
working on this project far too long, that annexed to my lack of knowledge,
accounts for my misconceptions.

My problem below (where order = order in list btw) was because I index'd all
*three* fields
The two example records are correctly unique! I needed to index AuthorId and
BookCatOrder only.
I was still able to enter data like this for BOOKCATEGORYS
AuthorId | CatId | BookCatOrder
1 | 1 | 2
1 | 2 | 2


Thank you!
~Philip
Nov 12 '05 #7
"Philip" <Ph****@afterap riltax.com> wrote in message
news:O%******** *******@nwrddc0 3.gnilink.net.. .
Fletcher,

I apologize because it DID work exactly as you explained! I think I've been working on this project far too long, that annexed to my lack of knowledge, accounts for my misconceptions.

My problem below (where order = order in list btw) was because I index'd all *three* fields
The two example records are correctly unique! I needed to index AuthorId and BookCatOrder only.
I was still able to enter data like this for BOOKCATEGORYS
AuthorId | CatId | BookCatOrder
1 | 1 | 2
1 | 2 | 2


Thank you!
~Philip

Glad you got it to work. Thanks for the feedback.
Fletcher

Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

37
4844
by: ajikoe | last post by:
Hello, Is anyone has experiance in running python code to run multi thread parallel in multi processor. Is it possible ? Can python manage which cpu shoud do every thread? Sincerely Yours, Pujo
4
4637
by: Frank Jona | last post by:
Intellisense with C# and a multi-file assembly is not working. With VB.NET it is working. Is there a fix availible? We're using VisualStudio 2003 Regards Frank
12
3847
by: * ProteanThread * | last post by:
but depends upon the clique: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=954drf%24oca%241%40agate.berkeley.edu&rnum=2&prev=/groups%3Fq%3D%2522cross%2Bposting%2Bversus%2Bmulti%2Bposting%2522%26ie%3DUTF-8%26oe%3DUTF-8%26hl%3Den ...
0
3758
by: frankenberry | last post by:
I have multi-page tiff files. I need to extract individual frames from the multi-page tiffs and save them as single-page tiffs. 95% of the time I receive multi-page tiffs containing 1 or more black and white CCITT4 compressed files (frames) inside the tiff. Every now and then I receive a mixture of black and white CCITT4 and JPEG compressed...
6
8148
by: cody | last post by:
What are multi file assemblies good for? What are the advantages of using multiple assemblies (A.DLL+B.DLL) vs. a single multi file assembly (A.DLL+A.NETMODULE)?
6
4872
by: Joe | last post by:
I have 2 multi-list boxes, 1 displays course categories based on a table called CATEGORIES. This table has 2 fields CATEGORY_ID, CATEGORY_NAME The other multi-list box displays courses based on a table called COURSES. This table has 2 fields CATEGORY_ID, COURSE_NAME. The CATEGORY_ID is a FK in COURSES and a PK in CATEGORIES. I want...
4
17831
by: mimmo | last post by:
Hi! I should convert the accented letters of a string in the correspondent letters not accented. But when I compile with -Wall it give me: warning: multi-character character constant Do the problem is the charset? How I can avoid this warning? But the worst thing isn't the warning, but that the program doesn't work! The program execute...
5
5724
by: bobwansink | last post by:
Hi, I'm relatively new to programming and I would like to create a C++ multi user program. It's for a project for school. This means I will have to write a paper about the theory too. Does anyone know a good place to start looking for some theory on the subject of multi user applications? I know only bits and pieces, like about...
0
2302
by: Sabri.Pllana | last post by:
We apologize if you receive multiple copies of this call for papers. *********************************************************************** 2008 International Workshop on Multi-Core Computing Systems (MuCoCoS'08) Barcelona, Spain, March 4 - 7, 2008; in conjunction with CISIS'08. <http://www.par.univie.ac.at/~pllana/mucocos08>...
1
9289
by: mknoll217 | last post by:
I am recieving this error from my code: The multi-part identifier "PAR.UniqueID" could not be bound. The multi-part identifier "Salary.UniqueID" could not be bound. The multi-part identifier "PAR.UniqueID" could not be bound. The multi-part identifier "PAR.PAR_Status" could not be bound. The multi-part identifier "Salary.New_Salary" could not...
0
7524
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7451
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7720
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7960
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7812
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5372
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5089
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1061
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
766
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.