VERY IMPORTANT...PLEASE HELP AS SOON AS POSSIBLE

[Rashrashetta]

I am to make a demo on my database project (using MS Access )....but
there are some problems in the refrential integrity....
I have two tables....ONE for the Customer with a composite primary key
( email and password)
and the SECOND for the Delivery which have a forigen key ( email and
pw ) for the Customer table...
Noe the relationship is one to many ( which means that a customer can
have many deliveries to him...where a delivery at a specific time by a
specific Employee number cannot be for more than one customer )
Now I did create the tables and added some records but now when I am
trying to ENFORCE THE REFRENTIAL INTEGRITY ...I have an error....
Is this because of the composite Primary Key???
And is it wrong that i allow both to be in the ( Indexed (Yes
Duplicate) )option??
So what can I do to solve this problem without changing the design (
As the demo is coming very soon )

Thanks alot for helping....
Yours,
Rsharashetta

 

[Tim Ferguson]

(e-mail address removed) (Rashrashetta) wrote in

I have two tables....ONE for the Customer with a composite primary key
( email and password)

this does not sound like a very good choice. In most systems, an email
address (or user name, even) should be unique. Furthermore, it must be
possible for a user to change the password, and that will create real
difficulties if it is part of a PK. Finally, you don't really want
passwords visible in clear text in a database table.

and the SECOND for the Delivery which have a forigen key ( email and
pw ) for the Customer table...

Correct, but not the choice I would have made as above.

Noe the relationship is one to many ( which means that a customer can
have many deliveries to him...where a delivery at a specific time by a
specific Employee number cannot be for more than one customer )
OK

Now I did create the tables and added some records but now when I am
trying to ENFORCE THE REFRENTIAL INTEGRITY ...I have an error....

What error? There is nothing logically wrong as long as the EM and PW
fields match exactly (i.e. if one is Text(32) then the other must be
Text(32) too, not Text(31) or (33) or anything else). And they must be in
the same order: EM+PW will not match PW+EM. If there are existing data in
the tables are there any Deliveries with non-matching data? Or Null values?

Is this because of the composite Primary Key???

No: a FK based on a compound key is fine.

And is it wrong that i allow both to be in the ( Indexed (Yes
Duplicate) )option??

As long as we are talking about the Deliveries table, not it's not wrong;
although I do not bother creating an index on a FK field(s) because Access
makes another one anyway.

So what can I do to solve this problem without changing the design

Actually, I would advise a rethink pretty soon; but your current design
should be able to work as described.


HTH


Tim F