By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,252 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

Relationship between tables with two field primary keys

P: 1
I have a table(a) with 2 fields: 1.code-2.description (yes no duplicates and primary keys, both of them)

another table(b) with three fields: 1.name (and the above 2 fields)

i have create a relationship one-to-many between table(a) and table(b) so as if table(a) has code=2450 and description whiskey, table(b) does not accept 2450 orange.(orange has code 2460 in table(a)

but the above does not work and table(b) accept it.

How can i fix it?

Kind regards
May 12 '12 #1
Share this Question
Share on Google+
3 Replies


zmbd
Expert Mod 5K+
P: 5,287
A few things:
  • Which field in Table(b) is the primary key?
  • Which fields have the relationships? (I can guess... but I'd like to know for sure)
  • When I've used compound keys, I've had to build a third table that includes all of the fields of interest.
  • I try to avoid compound keys... usually means I've missed a step in normalization. :(
  • IMHO - it will make your life easier to have the fields in table(b) reflect that they are foreign keys... that is to say, I would not use the exact same name for multiple fields across different tables. I tend to name my foreign keys something like: fk_tbla_code thus I can look at the field, know that in the table that the field is a (F)oreign(K)ey_(Primary key From Table(A))_(field named 'code')). Just makes writing queries and code so much clearer for me to follow.

So what I see here is:
Expand|Select|Wrap|Line Numbers
  1. tbl_a
  2. {code}-_Datatype? - PK
  3. {desc}- text - PK 
Expand|Select|Wrap|Line Numbers
  1. tbl_b
  2. {name}- text - PK???
  3. {code} - Datatype - FK? 1:m with what?
  4. {desc} - text - FK? 1:m with what?
I still think you're going to need a third table to link table(A) and table(B)

-z

Added:
After re-reading your question... I think you need to change how your tables are created. It really gives me the impression that the database is not normalized.
May 12 '12 #2

zmbd
Expert Mod 5K+
P: 5,287
OK,

In my test database I was able to re-create your issue... provided my assumptions are correct.

For their protection the following names have been changed:
"Code" interferes with this web-site's formatting for its safety it will now be known as "Item"
"Name" is a reserved word, to keep the coding Gremlins at bay... it will now be known as "PK_Name"

So that we know EXACTLY what we're working with I created the following:
Expand|Select|Wrap|Line Numbers
  1. Tbl_A
  2. [Item],Number(long), required, Indexed No Duplicate, PrimaryKey
  3. [Desc],text(50), required,zerolength=no, Indexed No Duplicate, PrimaryKey
  4. [MoreInfo],text(50)
-
Expand|Select|Wrap|Line Numbers
  1. Tbl_B
  2. [PK_Name],text(50), required,zerolength=no, Indexed No Duplicate, PrimaryKey
  3. [FK_TblA_Item],Number(long), required
  4. [FK_TblA_Desc],text(50), required
  5. [Other],text(50)
-
Notice that the two FK have only the "required", the restrictions for no duplicate and no nulls should be handled via Tbl_A
-
Now open the relationship editor,
Show both tables
Click on [Tbl_A]![Item] and drag it to the [Tbl_B]![FK_TblA_Item]
When the dialog box opens check mark "Enforce Referential Integrity"
In the second row of the dialog box table
Under the "Tbl_A" in the second row, select [desc]
Under the "Tbl_B" in the second row, select [FK_TblA_Desc]
"OK"
If you create the relationships seperately, you will run into the issue I think you were describing in your post. If you create them as shown, the the referential integrity will be enforced as expected.

You should now have:



So if you have data:
Expand|Select|Wrap|Line Numbers
  1. Tbl_A
  2. [2450][whiskey][somemoreinfo]
  3. [2460][orange][somethingelse]
Then in
Expand|Select|Wrap|Line Numbers
  1. Tbl_B
  2. good record>> [Name_1][2450][whiskey][otherinfo]
  3. Will Error>>[Name_2][2460][whiskey][othersomethingelse]
Row 3 in Tbl_B will cause an error that stating that a related record is needed (used my own data for this example):


I left Tbl_A open so you can see the record set.
Then I made two entries in Tbl_B. The first entry has no issues. However, as you can see, when I tried to save the second record, even though "110" is valid [Item] and "apple" is valid [desc], they are not in an associated record in tbl_a; thus, Access puked-up the required related record error.

CAUTION:
You could have the following records in tbl_b!
Expand|Select|Wrap|Line Numbers
  1. tbl_b
  2. monkeybutt - 2450 - whiskey
  3. lizardtail - 2450 - whiskey
  4. rabbitears - 2450 - whiskey
-
Similar tables work for me... once again, just a personal preference, I don't really like compound keys from a coding/SQL stand point as they make the coding longer; however, they do have their uses such as this one that can avoid some code checks... lots of opinions about compound vs natrual keys and I'll leave those arguments to people that do that for a living };-).
-Z
Attached Images
File Type: jpg 1toM_relationship.JPG (34.2 KB, 1888 views)
File Type: jpg 1toM_Table_Error.jpg (72.0 KB, 1904 views)
May 13 '12 #3

NeoPa
Expert Mod 15k+
P: 31,186
Some points to consider :
  1. A table can only have a single Primary Key, but it can have both multiple unique keys (indices) and indices with multiple fields (referred to as Compound indices).
  2. Consider what it is that makes 2450, 'Orange' wrong in your example. Is it because the [code] value already exists; The [Description] value already exists; or because both already exist? Because you use an example that duplicates both fields the explanation is without any information.
May 14 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.