473,732 Members | 1,924 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using two columns as a primary key

I am new to databases and thus to Access. I have a situation where I am
trying to figure out how to key some tables I am working with. Consider that
I have a database of my own records which are invoices I work on. These
invoices are from four companies with their own invoice numbers. So, I
cannot key by invoice number because there is a likelihood of invoice
numbers duplicated by different companies. The records in this table of
joined invoices are referenced in other tables, and I am not fond of using a
created index to reference each record.

Is it possible in Access (and SQL) to set the primary key to be a composite
of two columns? If so, how do I create a relationship between that table and
other tables? Say I have a separate table that keeps track of whether I've
been paid for processing each invoice. I would like to use the company name
and the company invoice number to create the relationship instead of my own
fabricated index. However, I'm not sure whether that can be done, never mind
the question of whether it is a good idea.

Thanks for the help, folks!

Paul
May 10 '06 #1
8 31537
To create a 2-field primary key, open your table in design view.
Select both fields (the "record selector" at left of field names.)
Click the Key icon on the toolbar.

If the 2 fields are not contiguous, you can also use the Indexes dialog to
create the multi-field index:
Open the Indexes dialog (View menu.)
In the first column, enter a name for the index (e.g. PrimaryKey)
In the 2nd column, choose the first field of the index.
In the lower pane, set Primary to Yes.
On the next *row* of the dialog, leave the Index Name blank, and choose the
2nd column.

To create relationships to other tables, drag the first field from this
table onto the matching field of the related table. When Access opens the
Create Relation dialog, you can match the 2nd field of the relation on the
2nd row of the dialog.

A 2-field index sometimes makes good sense, and the situation you describe
sounds like a good candidate. But if you have lots of other related tables,
which themselves have further relations, it is also possible to add an
AutoNumber as the primary key if you wish. You can still mark the Company
and InvoiceNumber fields as required, and create a Unique index on the pair
through the Indexes dialog described above. Just set Primary to No, and
Unique to Yes in the lower pane of the Indexes dialog.

--
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.

"Paul Hunter" <ba*********@co mcast.net> wrote in message
news:tf******** ************@co mcast.com...
I am new to databases and thus to Access. I have a situation where I am
trying to figure out how to key some tables I am working with. Consider
that
I have a database of my own records which are invoices I work on. These
invoices are from four companies with their own invoice numbers. So, I
cannot key by invoice number because there is a likelihood of invoice
numbers duplicated by different companies. The records in this table of
joined invoices are referenced in other tables, and I am not fond of using
a
created index to reference each record.

Is it possible in Access (and SQL) to set the primary key to be a
composite
of two columns? If so, how do I create a relationship between that table
and
other tables? Say I have a separate table that keeps track of whether I've
been paid for processing each invoice. I would like to use the company
name
and the company invoice number to create the relationship instead of my
own
fabricated index. However, I'm not sure whether that can be done, never
mind
the question of whether it is a good idea.

Thanks for the help, folks!

Paul

May 10 '06 #2
"Paul Hunter" <ba*********@co mcast.net> wrote in
news:tf******** ************@co mcast.com:
Consider that
I have a database of my own records which are invoices I work on.
These invoices are from four companies with their own invoice
numbers. So, I cannot key by invoice number because there is a
likelihood of invoice numbers duplicated by different companies.
The records in this table of joined invoices are referenced in
other tables, and I am not fond of using a created index to
reference each record.


I would never do this with a 2-column PK, even though it's the
theoretically appropriate way to do it.

Instead, I'd use my own routines to generate the invoice number and
include the company code as part of the number (either as text or as
numbers). You're going to have to write this kind of code anyway,
unless you're using an Autonumber for the second column of your
2-column key, and if you're doing that, I can't see any reason to
use a 2-column key in the first place.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 10 '06 #3
"David W. Fenton" <XX*******@dfen ton.com.invalid > wrote in
news:Xn******** *************** ***********@127 .0.0.1:
"Paul Hunter" <ba*********@co mcast.net> wrote in
news:tf******** ************@co mcast.com:
Consider that
I have a database of my own records which are invoices I work
on. These invoices are from four companies with their own
invoice numbers. So, I cannot key by invoice number because
there is a likelihood of invoice numbers duplicated by
different companies. The records in this table of joined
invoices are referenced in other tables, and I am not fond of
using a created index to reference each record.


I would never do this with a 2-column PK, even though it's the
theoretically appropriate way to do it.

Instead, I'd use my own routines to generate the invoice
number and include the company code as part of the number
(either as text or as numbers). You're going to have to write
this kind of code anyway, unless you're using an Autonumber
for the second column of your 2-column key, and if you're
doing that, I can't see any reason to use a 2-column key in
the first place.

David, the OP is not generating the invoice numbers. He's
entering invoice numbers he receives. The invoice number is a
foreign key to the system which created it.
--
Bob Quintal

PA is y I've altered my email address.
May 10 '06 #4
Bob Quintal <rq******@sympa tico.ca> wrote in
news:Xn******** **************@ 207.35.177.135:
"David W. Fenton" <XX*******@dfen ton.com.invalid > wrote in
news:Xn******** *************** ***********@127 .0.0.1:
"Paul Hunter" <ba*********@co mcast.net> wrote in
news:tf******** ************@co mcast.com:
Consider that
I have a database of my own records which are invoices I work
on. These invoices are from four companies with their own
invoice numbers. So, I cannot key by invoice number because
there is a likelihood of invoice numbers duplicated by
different companies. The records in this table of joined
invoices are referenced in other tables, and I am not fond of
using a created index to reference each record.


I would never do this with a 2-column PK, even though it's the
theoretically appropriate way to do it.

Instead, I'd use my own routines to generate the invoice
number and include the company code as part of the number
(either as text or as numbers). You're going to have to write
this kind of code anyway, unless you're using an Autonumber
for the second column of your 2-column key, and if you're
doing that, I can't see any reason to use a 2-column key in
the first place.


David, the OP is not generating the invoice numbers. He's
entering invoice numbers he receives. The invoice number is a
foreign key to the system which created it.


OK, I missed that.

If there are no child records for these entries, then I *might* go
with the 2-column PK (though I have the reservations noted below).

If there are child records, though, I'd definitely use a surrogate
key, then.

I would never turn over my PK generation to any outside source,
unless I knew that source was without question creating unique
numbers. As this cannot be guaranteed, I would never introduce that
dependency, as over time (like US Social Security Number) it's
likely to break.

That does not mean I wouldn't put a 2-column unique index on the two
fields, but I would not use them as the PK for my data table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 10 '06 #5
I created two primary keys for the tables (my invoice table and the
invoice-paid table). The invoice table is unchanged in its datasheet view:
selecting the + next to the company name on the left shows the values in the
invoice-paid table. However, that no longer exists in the invoice-paid
table. In the Relationships tool, two relationships are graphed between the
tables, both showing a one-to-many relationship. When I select the
relationships, the Edit Relationships dialog shows both relationships and
lists the relationship as one-to-one. Why doesn't Access recognize the
one-to-one relationship in anything other than the Edit Relationship dialog?

Thanks for your help, folks.
Paul

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:44******** **************@ per-qv1-newsreader-01.iinet.net.au ...
To create a 2-field primary key, open your table in design view.
Select both fields (the "record selector" at left of field names.)
Click the Key icon on the toolbar.

If the 2 fields are not contiguous, you can also use the Indexes dialog to
create the multi-field index:
Open the Indexes dialog (View menu.)
In the first column, enter a name for the index (e.g. PrimaryKey)
In the 2nd column, choose the first field of the index.
In the lower pane, set Primary to Yes.
On the next *row* of the dialog, leave the Index Name blank, and choose
the 2nd column.

To create relationships to other tables, drag the first field from this
table onto the matching field of the related table. When Access opens the
Create Relation dialog, you can match the 2nd field of the relation on the
2nd row of the dialog.

A 2-field index sometimes makes good sense, and the situation you describe
sounds like a good candidate. But if you have lots of other related
tables, which themselves have further relations, it is also possible to
add an AutoNumber as the primary key if you wish. You can still mark the
Company and InvoiceNumber fields as required, and create a Unique index on
the pair through the Indexes dialog described above. Just set Primary to
No, and Unique to Yes in the lower pane of the Indexes dialog.

--
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.

"Paul Hunter" <ba*********@co mcast.net> wrote in message
news:tf******** ************@co mcast.com...
I am new to databases and thus to Access. I have a situation where I am
trying to figure out how to key some tables I am working with. Consider
that
I have a database of my own records which are invoices I work on. These
invoices are from four companies with their own invoice numbers. So, I
cannot key by invoice number because there is a likelihood of invoice
numbers duplicated by different companies. The records in this table of
joined invoices are referenced in other tables, and I am not fond of
using a
created index to reference each record.

Is it possible in Access (and SQL) to set the primary key to be a
composite
of two columns? If so, how do I create a relationship between that table
and
other tables? Say I have a separate table that keeps track of whether
I've
been paid for processing each invoice. I would like to use the company
name
and the company invoice number to create the relationship instead of my
own
fabricated index. However, I'm not sure whether that can be done, never
mind
the question of whether it is a good idea.

Thanks for the help, folks!

Paul


May 10 '06 #6
The relationship is one-to-one because you linked the 2 primary keys. A
primary key field must be unique (by definition), hence there can be no more
than 1 value in each table (hence 1-1.)

Even a one-to-one relationship is directional. One table is the primary one
(probably Invoice in your case). There may or may not be a *related* record
in the InvoicePaid table. The converse cannot occur: you cannot have a
record in the InvoicePaid table that has no matching entry in the Invoice
table.

You can still set up the interface to show the other direction as well.
Open the InvoicePaid table in design view.
Open the Properties dialog (View menu.)
Set the Subdatasheet Name property to the name of your other table.
Now you can click the + beside an InvoicePaid record to see the invoice
data.

--
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.

"Paul Hunter" <ba*********@co mcast.net> wrote in message
news:PY******** *************** *******@comcast .com...
I created two primary keys for the tables (my invoice table and the
invoice-paid table). The invoice table is unchanged in its datasheet view:
selecting the + next to the company name on the left shows the values in
the invoice-paid table. However, that no longer exists in the invoice-paid
table. In the Relationships tool, two relationships are graphed between the
tables, both showing a one-to-many relationship. When I select the
relationship s, the Edit Relationships dialog shows both relationships and
lists the relationship as one-to-one. Why doesn't Access recognize the
one-to-one relationship in anything other than the Edit Relationship
dialog?

Thanks for your help, folks.
Paul

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:44******** **************@ per-qv1-newsreader-01.iinet.net.au ...
To create a 2-field primary key, open your table in design view.
Select both fields (the "record selector" at left of field names.)
Click the Key icon on the toolbar.

If the 2 fields are not contiguous, you can also use the Indexes dialog
to create the multi-field index:
Open the Indexes dialog (View menu.)
In the first column, enter a name for the index (e.g. PrimaryKey)
In the 2nd column, choose the first field of the index.
In the lower pane, set Primary to Yes.
On the next *row* of the dialog, leave the Index Name blank, and choose
the 2nd column.

To create relationships to other tables, drag the first field from this
table onto the matching field of the related table. When Access opens the
Create Relation dialog, you can match the 2nd field of the relation on
the 2nd row of the dialog.

A 2-field index sometimes makes good sense, and the situation you
describe sounds like a good candidate. But if you have lots of other
related tables, which themselves have further relations, it is also
possible to add an AutoNumber as the primary key if you wish. You can
still mark the Company and InvoiceNumber fields as required, and create a
Unique index on the pair through the Indexes dialog described above. Just
set Primary to No, and Unique to Yes in the lower pane of the Indexes
dialog.

--
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.

"Paul Hunter" <ba*********@co mcast.net> wrote in message
news:tf******** ************@co mcast.com...
I am new to databases and thus to Access. I have a situation where I am
trying to figure out how to key some tables I am working with. Consider
that
I have a database of my own records which are invoices I work on. These
invoices are from four companies with their own invoice numbers. So, I
cannot key by invoice number because there is a likelihood of invoice
numbers duplicated by different companies. The records in this table of
joined invoices are referenced in other tables, and I am not fond of
using a
created index to reference each record.

Is it possible in Access (and SQL) to set the primary key to be a
composite
of two columns? If so, how do I create a relationship between that table
and
other tables? Say I have a separate table that keeps track of whether
I've
been paid for processing each invoice. I would like to use the company
name
and the company invoice number to create the relationship instead of my
own
fabricated index. However, I'm not sure whether that can be done, never
mind
the question of whether it is a good idea.

May 11 '06 #7
"David W. Fenton" <XX*******@dfen ton.com.invalid > wrote in message
news:Xn******** *************** **********@127. 0.0.1...
Bob Quintal <rq******@sympa tico.ca> wrote in
news:Xn******** **************@ 207.35.177.135:
"David W. Fenton" <XX*******@dfen ton.com.invalid > wrote in
news:Xn******** *************** ***********@127 .0.0.1:
"Paul Hunter" <ba*********@co mcast.net> wrote in
news:tf******** ************@co mcast.com:

Consider that
I have a database of my own records which are invoices I work
on. These invoices are from four companies with their own
invoice numbers. So, I cannot key by invoice number because
there is a likelihood of invoice numbers duplicated by
different companies. The records in this table of joined
invoices are referenced in other tables, and I am not fond of
using a created index to reference each record.

I would never do this with a 2-column PK, even though it's the
theoretically appropriate way to do it.

I would never turn over my PK generation to any outside source,
unless I knew that source was without question creating unique
numbers. As this cannot be guaranteed, I would never introduce that
dependency, as over time (like US Social Security Number) it's
likely to break.

That does not mean I wouldn't put a 2-column unique index on the two
fields, but I would not use them as the PK for my data table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/


I've come across another related point many times lately. If you use an
outside source for you PK knowing it can be trusted to be unique, you still
run the risk of somebody making a typo when entering the data. If additional
records are also created in related tables, then when the typo is dicovered,
all of the related PK's in the other tables must be found and changed. It's
a pain. I'm now using Autonumber a lot more than I used to...
Fred Zuckerman
May 11 '06 #8
"Fred Zuckerman" <Zu********@sbc global.net> wrote in
news:fL******** **********@news svr14.news.prod igy.com:
I've come across another related point many times lately. If you
use an outside source for you PK knowing it can be trusted to be
unique, you still run the risk of somebody making a typo when
entering the data. If additional records are also created in
related tables, then when the typo is dicovered, all of the
related PK's in the other tables must be found and changed. It's
a pain. I'm now using Autonumber a lot more than I used to...


Well, except for the fact that it's not true, you've got a point.

If you set CASCADE UPDATES, it will update the child replicas.

But I don't believe in using as a PK data that can be updated as a
user. That data is way to important to allow users to be mucking
around with it. That's why I'm pretty much philosophically opposed
to natural keys in general.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
May 11 '06 #9

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

Similar topics

5
8215
by: Todd | last post by:
Data related to the query I'm working on is structured such that TableA and TableB are 1-many(optional). If an item on TableA has children on TableB, I need to use the Max(tstamp) from Table B in a condition, otherwise I need to use a tstamp from TableA (note:there are additional tables and conditions for this query, but this problem is based around these 2). I attempted having TableB (as B) "left outer joined" to TableA, and a condition...
4
14423
by: deko | last post by:
I have a Make Table query that creates a fairly large table. The Make Table query populates the new table with one AutoNumber field (which is taken form another unrelated table as part of the query). I want to make the AutoNumber field the Primary Key: DoCmd.OpenQuery "qryXL_To" ' this is the make table query DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxAcct ON tblXL_To (TxAcct_ID)" This seems to work okay, but it does not create a...
13
6530
by: Ron | last post by:
Hi all I'm deciding whether to use the PK also as an account number, invoice number, transaction number, etc that the user will see for the respective files. I understand that sometimes a number will be missing, which is not a problem for my purposes I don't think, but how would it be negative? Can randomly created PK autonumber fields be negative? Any way for a PK, autonumbered, *incremented* to be negative? TIA
2
3382
by: Ross Hamilton | last post by:
Can anyone help me with this small problem. I have a report that is divided into 3 Columns using the page setup, It displays the report fields in 3 separate columns (Desc & Pg Num) but it only displays the Page Header on the first column. Any thoughts?? regards,
3
1924
by: Jeff Amiel | last post by:
7.4.2 running on FreeBSD 5.4.2 I have a table (called "draft" ) that has a bigserial as the primary key. "check_id bigserial NOT NULL" I do NOT have an additional index on that column. I have a plpgsql function (stored procedure) that selects from that table based on the primary key field
13
1800
by: Ron | last post by:
Hi All, Okay, I've read previous suggestions about not showing the primary key to the user of forms. If a number is to be shown (let's say, customer number) it should be generated independently of the primary key (which is autonumbered and no dups). I think I've gotten that all accomplished. However, now how should I link files, or perform query's? Should these types of things be based on the customer number I generate or should I...
115
6250
by: LurfysMa | last post by:
Most of the reference books recommend autonum primary keys, but the Access help says that any unique keys will work. What are the tradeoffs? I have several tables that have unique fields. Can I use them as primary keys or should I define an autonum primary key? One table has information about the 50 states in the US. The table looks like this:
4
2280
by: onecorp | last post by:
I have a SQL table comprised of 31 columns. The first column is simply an id column, the next 30 columns are labelled ,.... The numerical columns have a tinyint type and the data stored is either 1 or null. I wish to count the number of times a one appears in one column simultaneously with another column: eg count the number of times 1 appears in column and 1 also appears in column in the same row:
8
16597
by: Imicola | last post by:
Hi, I want to use primary key IDs in my tables which are autonumbers, with a text prefix. I.e. for table tblVillage, the ID's will by V1, V2, V3... and for table tblHumanCases, the ID's will be H1, H2, H3... I have a few questions: 1. I have heard its not good to do this. Is this true and if so why? 2. I am also having problems creating the relationships to related tables due to the field types. In the parent table the field type...
2
1682
by: rneeraj | last post by:
I am preparing an Employee database and would like to add more than one education details column in a form for each employee. Eg: A person must be holding a masters degree. I would like to add details of his masters as well as bachelors.How do i do so. Thank You
0
8773
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
9234
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
9180
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6733
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 instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4548
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4805
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3259
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2721
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2177
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.