composite key

[TC]

Go to table design view, click the first field's record selector, then
shift-click the second field's record selector. Now >both< of them are
selected simultaneously. Now click the key icon (or whatever it is) to
define those two fields as the (composite) primary key.

There's nothing wrong in principle with composite primary keys. In some
cases, they can start to get unweildy - for example, if you got to the point
where the composite key required 5 fields (say). In that case, you might add
an autonumber for use as the primary key, & define the 5 other fields as a
unique index. Then you could use the autonumber (not the 5 fields) in other
places as required.

HTH,
TC

 

[middletree]

I have a table which I need to create for normalization purposes. It will
have the PK of two different tables, and that will be all. I cannot figure
out how to make them both the PK of this new table. I think you cal it a
composite key when you have two fields as the PK. So I looked up composite
key in the Help file and it didn't have anything to help me.

My question, then, is twofold:

1. How can I make a 2-part PK?

2. If I chose to let another field be the PK, that is, to add an autonumber
field to this compisite table, would that be better?

 

[Sam]

Hi Middletree,
Replies inline...

I have a table which I need to create for normalization purposes. It will
have the PK of two different tables, and that will be all. I cannot figure
out how to make them both the PK of this new table. I think you cal it a
composite key when you have two fields as the PK. So I looked up composite
key in the Help file and it didn't have anything to help me.

My question, then, is twofold:

1. How can I make a 2-part PK?

In table design view, select/highlight both fields, then click the key icon.
OR
view indexes, create the composite index and set primary to true

2. If I chose to let another field be the PK, that is, to add an autonumber
field to this compisite table, would that be better?

Depends on your application. Most often this would be inappropriate, e.g. it
may let you enrol the same student in a particular class more than once.

HTH
Sam

 

[middletree]

Thanks both of you. The problem with selecting both was, I selected both,
using the CTL key, but when I right-clicked, it wouldn't let me do both
fields as a PK. However, I discovered what you said: Click the PK icon on
the toolbar.

thanks

 

[Henry]

If you are into one table wth just two fields that make
up a composit key, each field being a foriegn key to some
other table, then you have a serious normalization
problem. If your tables a designed properly and
normalized to level 3 then you should not be in the
situation you described.
I strongly suggest you revisit your table scheme and
perform a normalization exercise on all your tables.
Failing to perform normalization will result in multiple
problems in the future as you progress through the DB
design and implementation.

Cheers,
Henry

 

[TC]

If you are into one table wth just two fields that make
up a composit key, each field being a foriegn key to some
other table, then you have a serious normalization
problem.

Nonsense.

tblStudent
Studend ID (PK)

tblClass
ClassID (PK)

tblStudentClass
StudentID ( composite )
ClassID (primary key)

A record in tblStudentClass is what tells the system that a given stiudent
is in a given class. Naturally there would often be extra fields in that
junction table; eg. enrollment date. But there is absolutely no reason why
it >must< have extra fields. So now we have one table, with just two fields,
each of which is an FK to some other table. Nothing wrong with that,
whatever.

TC


If your tables a designed properly and

 

[TC]

Hi Middletree,
Replies inline...
(snip)

Depends on your application. Most often this would be inappropriate, e.g. it
may let you enrol the same student in a particular class more than once.

When you replace a composite primary key with an autonumber (for example),
you need to define a unique index on the fields in question. This
re-establishes the relevant uniqueness constraint.

TC

 

[middletree]

I disagree. I have a lot to learn about Access, but I know enough from my
work with SQL Server to know that there is a use for this type of table. It
resolves many-to-many relationships.

 

[TC]

Precisely!

TC

I disagree. I have a lot to learn about Access, but I know enough from my
work with SQL Server to know that there is a use for this type of table. It
resolves many-to-many relationships.