Problem with composite key

[fiaolle]

Hi
Can I have a composite key and link it to a foriegn key which also is a
composite key? I have tried it, but when I'm trying to add referential
integrity to the link I get the error "No unique index found for the
referenced field of the primary table." What is the error, in other words? I
have both MovieID and MovieName as primarykeys because I have the same
number (ID) for more than one movie.

tbl_Movies
MovieID (PK)
MovieName (PK)

tbl_Categories
CategoryID (PK)
CategoryName

tbl_MovieCategories
MovieID (PK)
MovieName (PK)
CategoryID (PK)

Fia

 

[RoyVidar]

Hi
Can I have a composite key and link it to a foriegn key which also is
a composite key? I have tried it, but when I'm trying to add
referential integrity to the link I get the error "No unique index
found for the referenced field of the primary table." What is the
error, in other words? I have both MovieID and MovieName as
primarykeys because I have the same number (ID) for more than one
movie.

tbl_Movies
MovieID (PK)
MovieName (PK)

tbl_Categories
CategoryID (PK)
CategoryName

tbl_MovieCategories
MovieID (PK)
MovieName (PK)
CategoryID (PK)

Fia

How do you do this?

I think, if you drag'n'drop the whole primary key (the two fields)
from tbl_movies to tbl_moviecategories, you should only have to
ensure that both fields are listed for both tables, then hit the
enforce... thingie, and OK.

Or, if you just drag'n'drop one field, ensure you select both fields
for both tables in the lists in the edit relationships dialog before
hitting the enforce... thingie and OK.

 

[Dos Equis]

Fia,

First, I'm a learner, not an expert and all following advice should be
viewed as such.

It looks like you have three tables, none of which have the same PK
identified. i.e. 1 table has 1 field for PK, 1 table has 2 fields for
PK and 1 table has 3 fields as PK. This seems to tell Access that
pk1=pk1a=pk1a1, nothing is really a usable uniqe identifier.

If you need to use more than one field as an identifier, you might try
joining the two fields into a third to creat the unique identifier.
I'm sure there is an easier way (There almost always is) but my book
doesn't cover it so you get my experience not neccessarily the best
answer.

looking at the info provided, I would link
tbl_Movies.movieID(autonumber) to
tbl_MovieCategories.movieID(number, long integer)

one is assigned and the other refers to the first.

tbl_MovieCategories looks like a pivot table and unless you need the
data available as such is redundant. Should probably be part of a
query or report.

Hope this helps some,

Byron

 

[John Vinson]

Fia,

First, I'm a learner, not an expert and all following advice should be
viewed as such.

It looks like you have three tables, none of which have the same PK
identified. i.e. 1 table has 1 field for PK, 1 table has 2 fields for
PK and 1 table has 3 fields as PK. This seems to tell Access that
pk1=pk1a=pk1a1, nothing is really a usable uniqe identifier.

Um?

It would be VERY RARE for multiple tables to all have the same primary
key. This would be a one-to-one relationship - which is useful for
Subclassing, or for Field-Level Security Getaround, but rarely used
otherwise!

Each table has ITS OWN primary key; the three tables (appropriately)
have different primary keys. Each is a unique identifier within its
own table.

If you need to use more than one field as an identifier, you might try
joining the two fields into a third to creat the unique identifier.

This is unnecessary and in fact not a good idea. A Primary Key can
consist of up to TEN fields. The combination must be unique, but any
one of the multiple fields can have duplicates. Storing data
redundantly in a composite field is *never* necessary and violates at
least two principles - fields should be "atomic" and should not depend
on other fields.


John W. Vinson[MVP]

 

[Dos Equis]

John is right,

the primary key for table 1 is a foriegn key in table 2... Like I said,
I'm a lerner. While looking at the information, I got a little
confused. have not used composite keys yet so should probably have
stayed out of the conversation completly. consider my posts deleted.