Primary key to prevent duplicates

[acss]

I have an invoice DB and the primary key field is for the invoice number
which is unique and should not be duplicated. I have just received duplicate
invoices from differnt vendors so how does one work around this scenario?

invoiceID field=primary key unique
vendor number field=unique

 

[Jeff Boyce]

It sounds like you are describing a real-world situation in which
[InvoiceNumber] is NOT unique. Primary keys MUST be unique.

Therefore, you can't use [InvoiceNumber] as a primary key.

But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a
two-field primary key...

(this is a starting point for the discussion/argument about using Autonumber
primary keys -- "arbitrary" -- rather than "natural" keys).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[acss]

I just tried making both fields as primary and it worked fine since it
allowed the same invoice with a different vendor number. I can not use auto
number due to auditing concerns so this is what i needed. Thank you

It sounds like you are describing a real-world situation in which
[InvoiceNumber] is NOT unique. Primary keys MUST be unique.

Therefore, you can't use [InvoiceNumber] as a primary key.

But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a
two-field primary key...

(this is a starting point for the discussion/argument about using Autonumber
primary keys -- "arbitrary" -- rather than "natural" keys).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice DB and the primary key field is for the invoice number
which is unique and should not be duplicated. I have just received
duplicate
invoices from differnt vendors so how does one work around this scenario?

invoiceID field=primary key unique
vendor number field=unique

 

[Klatuu]

Just out of curiosity, what auditing concern prevents the use of autonumbers?
--
Dave Hargis, Microsoft Access MVP

I just tried making both fields as primary and it worked fine since it
allowed the same invoice with a different vendor number. I can not use auto
number due to auditing concerns so this is what i needed. Thank you

It sounds like you are describing a real-world situation in which
[InvoiceNumber] is NOT unique. Primary keys MUST be unique.

Therefore, you can't use [InvoiceNumber] as a primary key.

But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a
two-field primary key...

(this is a starting point for the discussion/argument about using Autonumber
primary keys -- "arbitrary" -- rather than "natural" keys).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice DB and the primary key field is for the invoice number
which is unique and should not be duplicated. I have just received
duplicate
invoices from differnt vendors so how does one work around this scenario?

invoiceID field=primary key unique
vendor number field=unique

 

[acss]

I have read many postings of the tendancy/possibility of skipping records and
for this reason i do not have any autonumber fields. It would have been
better in my DB design to utilize the autonumber for one of my tables but i
did not want to take that chance.

Just out of curiosity, what auditing concern prevents the use of autonumbers?
--
Dave Hargis, Microsoft Access MVP

I just tried making both fields as primary and it worked fine since it
allowed the same invoice with a different vendor number. I can not use auto
number due to auditing concerns so this is what i needed. Thank you

It sounds like you are describing a real-world situation in which
[InvoiceNumber] is NOT unique. Primary keys MUST be unique.

Therefore, you can't use [InvoiceNumber] as a primary key.

But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a
two-field primary key...

(this is a starting point for the discussion/argument about using Autonumber
primary keys -- "arbitrary" -- rather than "natural" keys).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice DB and the primary key field is for the invoice number
which is unique and should not be duplicated. I have just received
duplicate
invoices from differnt vendors so how does one work around this scenario?

invoiceID field=primary key unique
vendor number field=unique

 

[Klatuu]

I think you may have some misconceptions about autonumbers.
You don't skip any records, but records may or may not be sequentially
numbered without gaps.

For starters, autonumber fields do not have to be sequential by 1. You can
set them as random, so that random numbers are used and you can set the
increment to some value other than 1, so they may be numbered 10, 15, 20, 25,
etc.

You can guarantee there will almost always be gaps in the numbering. That is
because the number is assigned as soon as the first character of a record is
entered into the record, but if the record is undone before it is saved, the
record is discarded, but the number is not reused. The same goes for
deleting records. If you delete a record, its number is not reused.

But, even with all that said, autonumbers should have no meaning in a
relational database. They should never be exposed to a user. Their
intended use is only as a surrogate primary key. It is also useful for using
in child tables as a foreign key to the parent.

As Jeff said, anytime someone says autonumber, there is a discussion on
whether they should ever be used or not. I am in favor of them. My
reasoning is that it easily makes a unique primary key. If you have to have
multiple fields to create a unique value, particularly if they are not of the
same data type, it takes more space in the database and to some degree
degrades performance. I have seen one instance where a 5 field key was used
that had a text field, a date field, and 3 long integer fields to make up a
primary key. It wasn't too bad in Jet, but when we upsized that app to SQL
Sever, updates and deletes got really slow. The extra time as in rebuilding
indexes. It also means you have to have all those fields in any child
records or build your own unique value to relate the child to the parent.
But, that is just my opinion.

So, I don't see how that is an audit issue. It would be no different than a
natural key. How will you know if a record was deleted? If you need true
record level auditing, you need to implement auditing in your application to
track who added a record and when, every change to every record with who and
when and if a record is deleted, who and when. Otherwise, there really is no
way to audit a database other than comparing to a backup copy and that
doesn't give you any real information on how the changes happened.

This is of course considerable overhead, but for regulated or sensitive data
applications, it is the better way to do it.

So the short of it is, there is no more risk using Autonumbers than there is
using natural keys.
--
Dave Hargis, Microsoft Access MVP

I have read many postings of the tendancy/possibility of skipping records and
for this reason i do not have any autonumber fields. It would have been
better in my DB design to utilize the autonumber for one of my tables but i
did not want to take that chance.

Just out of curiosity, what auditing concern prevents the use of autonumbers?
--
Dave Hargis, Microsoft Access MVP

I just tried making both fields as primary and it worked fine since it
allowed the same invoice with a different vendor number. I can not use auto
number due to auditing concerns so this is what i needed. Thank you

:

It sounds like you are describing a real-world situation in which
[InvoiceNumber] is NOT unique. Primary keys MUST be unique.

Therefore, you can't use [InvoiceNumber] as a primary key.

But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a
two-field primary key...

(this is a starting point for the discussion/argument about using Autonumber
primary keys -- "arbitrary" -- rather than "natural" keys).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice DB and the primary key field is for the invoice number
which is unique and should not be duplicated. I have just received
duplicate
invoices from differnt vendors so how does one work around this scenario?

invoiceID field=primary key unique
vendor number field=unique

 

[acss]

I would like to thank you for the highly detailed explaination and in future
designs , it will be considered since as stated it would have been preferred
to use autonumber on at least one field. My error was to pass judgement on
what is read in postings than from actual experience practiced. It is always
an experience on this news groups and I am profoundly grateful for the
professional support I receive on this site.

I think you may have some misconceptions about autonumbers.
You don't skip any records, but records may or may not be sequentially
numbered without gaps.

For starters, autonumber fields do not have to be sequential by 1. You can
set them as random, so that random numbers are used and you can set the
increment to some value other than 1, so they may be numbered 10, 15, 20, 25,
etc.

You can guarantee there will almost always be gaps in the numbering. That is
because the number is assigned as soon as the first character of a record is
entered into the record, but if the record is undone before it is saved, the
record is discarded, but the number is not reused. The same goes for
deleting records. If you delete a record, its number is not reused.

But, even with all that said, autonumbers should have no meaning in a
relational database. They should never be exposed to a user. Their
intended use is only as a surrogate primary key. It is also useful for using
in child tables as a foreign key to the parent.

As Jeff said, anytime someone says autonumber, there is a discussion on
whether they should ever be used or not. I am in favor of them. My
reasoning is that it easily makes a unique primary key. If you have to have
multiple fields to create a unique value, particularly if they are not of the
same data type, it takes more space in the database and to some degree
degrades performance. I have seen one instance where a 5 field key was used
that had a text field, a date field, and 3 long integer fields to make up a
primary key. It wasn't too bad in Jet, but when we upsized that app to SQL
Sever, updates and deletes got really slow. The extra time as in rebuilding
indexes. It also means you have to have all those fields in any child
records or build your own unique value to relate the child to the parent.
But, that is just my opinion.

So, I don't see how that is an audit issue. It would be no different than a
natural key. How will you know if a record was deleted? If you need true
record level auditing, you need to implement auditing in your application to
track who added a record and when, every change to every record with who and
when and if a record is deleted, who and when. Otherwise, there really is no
way to audit a database other than comparing to a backup copy and that
doesn't give you any real information on how the changes happened.

This is of course considerable overhead, but for regulated or sensitive data
applications, it is the better way to do it.

So the short of it is, there is no more risk using Autonumbers than there is
using natural keys.
--
Dave Hargis, Microsoft Access MVP

I have read many postings of the tendancy/possibility of skipping records and
for this reason i do not have any autonumber fields. It would have been
better in my DB design to utilize the autonumber for one of my tables but i
did not want to take that chance.

Just out of curiosity, what auditing concern prevents the use of autonumbers?
--
Dave Hargis, Microsoft Access MVP


:

I just tried making both fields as primary and it worked fine since it
allowed the same invoice with a different vendor number. I can not use auto
number due to auditing concerns so this is what i needed. Thank you

:

It sounds like you are describing a real-world situation in which
[InvoiceNumber] is NOT unique. Primary keys MUST be unique.

Therefore, you can't use [InvoiceNumber] as a primary key.

But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a
two-field primary key...

(this is a starting point for the discussion/argument about using Autonumber
primary keys -- "arbitrary" -- rather than "natural" keys).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice DB and the primary key field is for the invoice number
which is unique and should not be duplicated. I have just received
duplicate
invoices from differnt vendors so how does one work around this scenario?

invoiceID field=primary key unique
vendor number field=unique

 

[Klatuu]

Thanks for the kudos. I, too, learn every day from these newsgroups.
--
Dave Hargis, Microsoft Access MVP

I would like to thank you for the highly detailed explaination and in future
designs , it will be considered since as stated it would have been preferred
to use autonumber on at least one field. My error was to pass judgement on
what is read in postings than from actual experience practiced. It is always
an experience on this news groups and I am profoundly grateful for the
professional support I receive on this site.

I think you may have some misconceptions about autonumbers.
You don't skip any records, but records may or may not be sequentially
numbered without gaps.

For starters, autonumber fields do not have to be sequential by 1. You can
set them as random, so that random numbers are used and you can set the
increment to some value other than 1, so they may be numbered 10, 15, 20, 25,
etc.

You can guarantee there will almost always be gaps in the numbering. That is
because the number is assigned as soon as the first character of a record is
entered into the record, but if the record is undone before it is saved, the
record is discarded, but the number is not reused. The same goes for
deleting records. If you delete a record, its number is not reused.

But, even with all that said, autonumbers should have no meaning in a
relational database. They should never be exposed to a user. Their
intended use is only as a surrogate primary key. It is also useful for using
in child tables as a foreign key to the parent.

As Jeff said, anytime someone says autonumber, there is a discussion on
whether they should ever be used or not. I am in favor of them. My
reasoning is that it easily makes a unique primary key. If you have to have
multiple fields to create a unique value, particularly if they are not of the
same data type, it takes more space in the database and to some degree
degrades performance. I have seen one instance where a 5 field key was used
that had a text field, a date field, and 3 long integer fields to make up a
primary key. It wasn't too bad in Jet, but when we upsized that app to SQL
Sever, updates and deletes got really slow. The extra time as in rebuilding
indexes. It also means you have to have all those fields in any child
records or build your own unique value to relate the child to the parent.
But, that is just my opinion.

So, I don't see how that is an audit issue. It would be no different than a
natural key. How will you know if a record was deleted? If you need true
record level auditing, you need to implement auditing in your application to
track who added a record and when, every change to every record with who and
when and if a record is deleted, who and when. Otherwise, there really is no
way to audit a database other than comparing to a backup copy and that
doesn't give you any real information on how the changes happened.

This is of course considerable overhead, but for regulated or sensitive data
applications, it is the better way to do it.

So the short of it is, there is no more risk using Autonumbers than there is
using natural keys.
--
Dave Hargis, Microsoft Access MVP

I have read many postings of the tendancy/possibility of skipping records and
for this reason i do not have any autonumber fields. It would have been
better in my DB design to utilize the autonumber for one of my tables but i
did not want to take that chance.

:

Just out of curiosity, what auditing concern prevents the use of autonumbers?
--
Dave Hargis, Microsoft Access MVP


:

I just tried making both fields as primary and it worked fine since it
allowed the same invoice with a different vendor number. I can not use auto
number due to auditing concerns so this is what i needed. Thank you

:

It sounds like you are describing a real-world situation in which
[InvoiceNumber] is NOT unique. Primary keys MUST be unique.

Therefore, you can't use [InvoiceNumber] as a primary key.

But if [InvoiceNumber] and [Vendor] is ALWAYS unique, you could have a
two-field primary key...

(this is a starting point for the discussion/argument about using Autonumber
primary keys -- "arbitrary" -- rather than "natural" keys).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an invoice DB and the primary key field is for the invoice number
which is unique and should not be duplicated. I have just received
duplicate
invoices from differnt vendors so how does one work around this scenario?

invoiceID field=primary key unique
vendor number field=unique

 

[George]

The Autonumber field is used as the primary key field to assure that a
"unique" *record* exists. No two records in the same table will ever have
the same primary key. You may also want to create another index in the same
table that is not a primary key. This may be one non-duplicate field or
multiple fields that are not duplicated in the same *unique* combination.
This would solve your immediate problem. It would let you have Invoice 100
with any number of different vendor IDs but no specific vendorID could have
more than one Invoice 100. Invoice number is *probably* a text field if you
are tracking received invoices. If you are creating the invoices for
multiple entities then you might have a key field for an invoice number but
it would be better to make the invoice number a text field which would allow
both alphabetic and numeric characters to be used in an invoice "number".

A two field unique index would allow these combinations of data items in the
two fields (four records) :

A - A
A - B
B - A
B - B

but you would not be able to duplicate any of these combinations again in
this indexed table. The index can be extended to include 3, 4, ... or more
fields.

Information contained in non-key fields can change. This might result in
relinking with bad results if any meaningful field were also a key field.
For instance autonumber record 36 might contain a single woman's name but if
she marries and changes her name she is still the same "unique" person after
the name change. Thus the use of separate key and data fields.

Another thing to think about concerning key fields is that the key field
itself should have absolutely no significance. The only significance it has
is that it is "unique". Sometimes users try to force meaning by making a
"smart" key field which never helps and may hurt SQL operations. Any other
kind of significance than "unique" does not belong in the key field. It
belongs in another field in the record. This fact is always implied but is
not so often expressed.

When you have any pair of parent-child relationships linked you need to keep
in mind these parallel concepts:

1) A primary key in the parent table is used as a lookup source by a child
table that picks up the primary key and stores it into the child table as a
foreign key.
2) Using referential integrity a parent record must exist before a child
record can be added.
3) When you are using forms to maintain your data the parent record is
displayed on a main form (single record form) while the child record is
embedded in a subform (continuous record form) displayed on the parent form.
4) With a single one-to-many (parent-child) relationship the "one" side has
the primary key and is on the main form while the "many" side has the
foreign key and is on the subform.
5) A many-to-many relationship requires use of an intermediate "resolver
table" containing foreign keys from each of the primary keys in the other
related tables. It is a compound relationship formed by joining a
one-to-many relationship joined to a many-to-one relationship.
6) The combo-box control will handle linking of primary and foreign key
fields so that this function is hidden from the user. You can display the
key fields on your student forms so that you can see what is taking place
when you naviaget through forms or add records..

Practice setting up a single parent-child relationship and creating the
related forms to be able to add, change, or delete records in both tables.
If you can do this you have successfully mastered an important phase of
learning to develop Access relational databases. As you add more related
tables to your database the same process will be repeated again and again.

An autonumber key field makes a key field unique to each table. An advanced
concept is the use of a function to return the next sequential integer to
create a primary key numeric field rather than the autonumber datatype which
makes a primary number unique to the database rather than a single table. A
unique primary key would then exist for all records. No two records in any
table would ever share a common primary key. This makes replication and
database reorganization a lot easier. But it's not necessary for an Access
beginner to understand this.

George - HAL-PC - Houston