473,570 Members | 2,857 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Composite Primary Key

I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?

Thank you in advance!

Still a newbie,
-Thomas
Nov 13 '05 #1
18 12631
Okay, you have 2 table with primary keys, and a junction table that has
foreign keys to the other 2 tables. You could select the 2 fields together
in Table Design view, and click the Key icon on the toolbar to make a
2-field primary key (i.e. the primary key consists of the combination of the
2 fields... there are not 2 primary keys.)

That would be a good approach if the junction table should not contain
duplicates. For example, if the 2 tables are Client and Newsletter, it would
be a good idea not to have the same person on the newsletter group twice.

It would not be a good idea where duplicates are desired. For example, if
the 2 tables are Member and Position, the person might be chairman in 2002
and again in 2005, so you could validly have 2 entries where the member
holds the same position, so using the 2-field primary key would not be
suitable. Instead, you could add an AutoNumber to the 3rd field and use that
as primary key.

If the 3rd table is also likely to be involved in relations to other tables,
you might elect to give it an AutoNumber rather than handle multi-field
joins to other tables, which potentially could themselves have further joins
to other tables and so on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Thomas A. Anderson" <NO************ @hotmail.com> wrote in message
news:di******** **@gnus01.u.was hington.edu...
I am a bit confused in creating a composite primary key. I have three
table with two of the tables containing primary keys. I have two of the
tables (each with a primary key) having one to many relations with the
table containing no primary key. Do I just create two primary keys on the
table that does not contain any primary key for this to become a composite
primary key?

Thank you in advance!

Still a newbie,
-Thomas

Nov 13 '05 #2
Per Thomas A. Anderson:
I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?


Being easily confused myself, I never, ever use either composite primary keys or
any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are strategic
problems with AutoNumber for a given situation, a Long field that I populate
uniquely).

It's been 12 years and I've never had a problem - and it seems to me like it's a
*lot* simpler, especially when I've been away from an app for a year or so and
have to understand it quickly again.
--
PeteCresswell
Nov 13 '05 #3
On Wed, 12 Oct 2005 07:31:28 -0700, "(PeteCresswell )" <a@b.c.invalid. USA> wrote:
Per Thomas A. Anderson:
I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?


Being easily confused myself, I never, ever use either composite primary keys or
any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are strategic
problems with AutoNumber for a given situation, a Long field that I populate
uniquely).

It's been 12 years and I've never had a problem - and it seems to me like it's a
*lot* simpler, especially when I've been away from an app for a year or so and
have to understand it quickly again.


But surely you must sometimes use a composite unique index, or how do you
control unwanted duplicates?

Nov 13 '05 #4

polite person wrote:
On Wed, 12 Oct 2005 07:31:28 -0700, "(PeteCresswell )" <a@b.c.invalid. USA> wrote:
Per Thomas A. Anderson:
I am a bit confused in creating a composite primary key. I have three table
with two of the tables containing primary keys. I have two of the tables
(each with a primary key) having one to many relations with the table
containing no primary key. Do I just create two primary keys on the table
that does not contain any primary key for this to become a composite primary
key?


Being easily confused myself, I never, ever use either composite primary keys or
any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are strategic
problems with AutoNumber for a given situation, a Long field that I populate
uniquely).

It's been 12 years and I've never had a problem - and it seems to me like it's a
*lot* simpler, especially when I've been away from an app for a year or so and
have to understand it quickly again.


But surely you must sometimes use a composite unique index, or how do you
control unwanted duplicates?


We're discussing something like this at the moment on another project,
where every Employee has a PIN (Personal Identification Number). PINs
are, by their nature, unique, and therefore a good candidate for
Primary Key. But what happens if the company decide in the future to
re-use PINs from retired or ex-employees?

In answer to your question, there is no reason why you cannot have a
composite unique index in addition to your PK. In fact, if there are
business rules like this, I prefer to enforce them at database level
(though currently I do much more work on SQL Server which has some
useful tools lacking in Access such as AFTER and INSTEAD OF Triggers)

Edward

Nov 13 '05 #5
On 12 Oct 2005 06:59:15 -0700, te********@hotm ail.com wrote:

polite person wrote:

But surely you must sometimes use a composite unique index, or how do you
control unwanted duplicates?

In answer to your question, there is no reason why you cannot have a
composite unique index in addition to your PK. In fact, if there are
business rules like this, I prefer to enforce them at database level
(though currently I do much more work on SQL Server which has some
useful tools lacking in Access such as AFTER and INSTEAD OF Triggers)


Sorry, the point of my comment was that surely Pete must already be using composite unique indexes
therefore I was unable to see why he found it too difficult to ever use a composite primary key.

The question of whether or when to use articifical versus natural keys is a different one,
discussed here before. In the present dogmatic climate of the group discussions I don't
think reviving it would be useful.

Nov 13 '05 #6

(PeteCresswell) wrote:
I slavishly assign an AutoNumber PK


The availability of a unique integer that identifies each record in the
database, to the database, and that is available as an identifier to
anything that connects to the database enhances the reliability and
validity of the database.

Sometimes, I think, beginners try to make this PrimaryKey identify Joe,
Mary, or Tuck Number 2005-4. PrimaryKeys have nothing to do with
identifying Joe, Mary, or Truck 2005-04. They are the first designated
unique indexes of tables; as such they order the table in certain
situations (such as JET compact), determine the physical order of the
table in certain circumstances (clustered indexes), and the outcome of
the output of tables in many circumstances, and identify records,
nothing else but records.

I think your slavish assignment is one of the simplest and best things
one can do to create a sound database.

In my databases these are many of these AutoNumber PKs which I never
use explicitly. Does this slow my dbs? Not that I have noticed. Can
Access always find my records? Yes.

Nov 13 '05 #7
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in
news:43******** *************** @per-qv1-newsreader-01.iinet.net.au :
Okay, you have 2 table with primary keys, and a junction table
that has foreign keys to the other 2 tables. You could select the
2 fields together in Table Design view, and click the Key icon on
the toolbar to make a 2-field primary key (i.e. the primary key
consists of the combination of the 2 fields... there are not 2
primary keys.)

That would be a good approach if the junction table should not
contain duplicates. For example, if the 2 tables are Client and
Newsletter, it would be a good idea not to have the same person on
the newsletter group twice.

It would not be a good idea where duplicates are desired. For
example, if the 2 tables are Member and Position, the person might
be chairman in 2002 and again in 2005, so you could validly have 2
entries where the member holds the same position, so using the
2-field primary key would not be suitable. Instead, you could add
an AutoNumber to the 3rd field and use that as primary key.


Er, why add an AutoNumber for that? Wouldn't you instead (assuming
you're going with natural keys) add the year to the composite key?
You'd need that as a unique index even if you added the AutoNumber.
I'd only add an AutoNumber to a junction table if the PK of the
junction table is a foreign key in a child table (which can
certainly happen, of course).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #8
"(PeteCresswell )" <a@b.c.invalid. USA> wrote in
news:ua******** *************** *********@4ax.c om:
Per Thomas A. Anderson:
I am a bit confused in creating a composite primary key. I have
three table with two of the tables containing primary keys. I
have two of the tables (each with a primary key) having one to
many relations with the table containing no primary key. Do I
just create two primary keys on the table that does not contain
any primary key for this to become a composite primary key?


Being easily confused myself, I never, ever use either composite
primary keys or any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are
strategic problems with AutoNumber for a given situation, a Long
field that I populate uniquely).

It's been 12 years and I've never had a problem - and it seems to
me like it's a *lot* simpler, especially when I've been away from
an app for a year or so and have to understand it quickly again.


But surely you create unique indexes on those natural key
candidates? If not, how do you prevent the addition of duplicates?

Junction tables are the only place where I'll tend to use composite
keys (though they are hardly ever "natural keys," since the joins
are almost always on Autonumber fields), except where there is some
natural unique index that needs to be and *can* be enforced.

You may not use those as *primary keys* and use surrogate keys
instead (i.e., Autonumber), but you still need the unique index, or
your schema is really incomplete.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9
David W. Fenton wrote:
"(PeteCresswell )" <a@b.c.invalid. USA> wrote in
news:ua******** *************** *********@4ax.c om:
Per Thomas A. Anderson:
I am a bit confused in creating a composite primary key. I have
three table with two of the tables containing primary keys. I
have two of the tables (each with a primary key) having one to
many relations with the table containing no primary key. Do I
just create two primary keys on the table that does not contain
any primary key for this to become a composite primary key?


Being easily confused myself, I never, ever use either composite
primary keys or any "real" field for a primary key.

Instead, I slavishly assign an AutoNumber PK (or, if there are
strategic problems with AutoNumber for a given situation, a Long
field that I populate uniquely).

It's been 12 years and I've never had a problem - and it seems to
me like it's a *lot* simpler, especially when I've been away from
an app for a year or so and have to understand it quickly again.


But surely you create unique indexes on those natural key
candidates? If not, how do you prevent the addition of duplicates?

Junction tables are the only place where I'll tend to use composite
keys (though they are hardly ever "natural keys," since the joins
are almost always on Autonumber fields), except where there is some
natural unique index that needs to be and *can* be enforced.

You may not use those as *primary keys* and use surrogate keys
instead (i.e., Autonumber), but you still need the unique index, or
your schema is really incomplete.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc


I suppose it never hurts to have the composite key around to help
ensure uniqueness. Any extra checks and balances that Access can
provide are good. I never want to trap an error saying I have a key
violation so I end up with the same amount of coding either way. The
composite key is just a final safety check. Even better would be not
to present choices on the form that could cause a duplicate in a
junction table. If anyone is allowed to edit the junction table
directly the composite key would be more important. I just don't think
there's a tempest in this teapot.

James A. Fortune

Nov 13 '05 #10

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

Similar topics

0
2629
by: Girish Agarwal | last post by:
--0-474210375-1058976151=:31789 Content-Type: text/plain; charset=us-ascii Content-Id: Content-Disposition: inline Note: forwarded message attached. __________________________________
5
6918
by: John | last post by:
Specifically for joint tables... tblStudents tblClasses tblClasses_Students Is it be good programming to use a composite primary key in tblClasses_Students (where the key is ClassID and StudentID)? I enventually will convert the Access db to an SQL db. I've always used single primary keys based on long integers, usually autonumbered.
10
16499
by: deko | last post by:
I understand it's possible to make a composite Primary Key by holding down the control key and selecting multiple fields, then right-clicking and selecting Primary Key. But I've heard that's not a good idea. What I want to do is use table constraints to ensure only unique transactions - defined by a combination of amount, date, account and...
4
4044
by: Ismail Rajput | last post by:
Is there any option we can use Composite DataKeyField in the DataList and DataGrid?
2
2227
by: Peter Lindquist | last post by:
I had a fun issue this morning, and now I'm wondering if I violated a rule I didn't know about. Any insight would be much appreciated. All operations involving this particular table may be assumed to have functioned normally before today. bash-2.05a$ cat PG_VERSION 7.3 Last night, we added 'index_t_payment_param' to this table....
7
5601
by: Ronald S. Cook | last post by:
My client manager likes concatenated/composite primary keys. I don't. Can anyone forward any arguments pro or con? Thanks, Ron
4
2959
by: Wolfgang Keller | last post by:
Hello, so far it seems to me as if the only ORM module for Python which supports composite primary/foreign keys was SQLAlchemy. Which looks a little bit "overbloated" for my needs: I "just" need to be able to define a "logical model" (à la UML) in Python and have the ORM connect to a database (running on PostgreSQL in my case) which uses a...
2
3500
by: lfhenry | last post by:
Hi All, I am about to make some changes to a process we have. The new process will have a new table who's job it is to store temporarily data such as customer, clerkno,productno, storeno , amount, time. On a new request from a front-end i will select against the primary keys to ensure no other request for the same data has been made. This...
54
3981
by: csolomon | last post by:
Hello: I was wondering if I could get some input on how to address a design issue, involving my composite table. I have one portion of my project complete. The following forms and reports I will add, piggyback off of my existing design. The part I have already completed allows my users to create a design sample; this is made up of...
0
7637
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...
1
7702
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8000
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...
0
6332
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5523
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
5247
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...
0
3684
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3671
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1238
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.