composite key

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

 

[Jeff Boyce]

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]

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.

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

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.

 

[Armen Stein]

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

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

 

[patti]

thanks for taking the time to help. it is greatly appreciated.