composite key

  • Thread starter Thread starter patti
  • Start date Start date
P

patti

I have never dealt w/ a composite key before. I have a table with primary
keys of itemsku and date. Does this Composite Key guarantee that there will
be no duplicates?

thanks.
 
Patti

A table has only one "primary key". That key can be a composite of more
than one field/column.

Before we can guarantee no duplicates, you need to tell us more about your
[itemsku] and [date] values.

Yes, if the two fields together are your primary key, Access will prevent
any duplication OF THOSE COMBINED FIELDS. You could still have the same
[itemsku] in multiple records, and you could still have the same [date] in
multiple records, but only one instance of the unique combination of these.

By the way, Access treats the word "date" as a reserved word. You'll never
know what Access is going to use (and Access may not understand what you
want to use) -- change the name of this field to something more meaningful,
and not a reserved word.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
thanks jeff. i inherited undocumented databases. benn grappling w/ so many
issues. Would love to rebuild them all but time and money disallow. I do
appreciate your pointing out such things as reserved words (that i do know
about). I am always looking to learn, so anytime a flaw is explained, i perk
up.

In the table design, i saw the litlle key symbol next to itemsku and date
fields, hence my error in referring to primary keys.
Those 2 fields together are the primary key, and you have explained that
because of that, duplicates will be prevented - no itemsku will have a
duplicate date and no date will have a duplicate itemsku.

Thnaks to you and all who help.


Jeff Boyce said:
Patti

A table has only one "primary key". That key can be a composite of more
than one field/column.

Before we can guarantee no duplicates, you need to tell us more about your
[itemsku] and [date] values.

Yes, if the two fields together are your primary key, Access will prevent
any duplication OF THOSE COMBINED FIELDS. You could still have the same
[itemsku] in multiple records, and you could still have the same [date] in
multiple records, but only one instance of the unique combination of these.

By the way, Access treats the word "date" as a reserved word. You'll never
know what Access is going to use (and Access may not understand what you
want to use) -- change the name of this field to something more meaningful,
and not a reserved word.

Regards

Jeff Boyce
Microsoft Office/Access MVP

patti said:
I have never dealt w/ a composite key before. I have a table with primary
keys of itemsku and date. Does this Composite Key guarantee that there
will
be no duplicates?

thanks.
Click to expand...
Click to expand...
 
In the table design, i saw the litlle key symbol next to itemsku and date
fields, hence my error in referring to primary keys.
Click to expand...

Hi Patti,

The little key symbol does indeed indicate that the field is part of a
primary key for the table. So that wasn't an error. I think Jeff was
just clarifying terminology.

As Jeff indicated, Access will ensure that the group of fields
comprising a primary key will be unique. Also, all the fields in a
primary key must have a value - Nulls aren't allowed.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top