Relationship between tables with two field primary keys

[SPYROS POULIS]

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

[zmbd]

Linking table required

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))_(fiel d named 'code')). Just makes writing queries and code so much clearer for me to follow.

So what I see here is:

tbl_a
{code}-_Datatype? - PK
{desc}- text - PK

tbl_b
{name}- text - PK???
{code} - Datatype - FK? 1:m with what?
{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.

[zmbd]

Try this

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:

Tbl_A
[Item],Number(long), required, Indexed No Duplicate, PrimaryKey
[Desc],text(50), required,zerolength=no, Indexed No Duplicate, PrimaryKey
[MoreInfo],text(50)

-

Tbl_B
[PK_Name],text(50), required,zerolength=no, Indexed No Duplicate, PrimaryKey
[FK_TblA_Item],Number(long), required
[FK_TblA_Desc],text(50), required
[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:
[IMGnothumb]http://bytes.com/attachments/attachment/6376d1336922050/1tom_relationsh ip.jpg[/IMGnothumb]

So if you have data:

Tbl_A
[2450][whiskey][somemoreinfo]
[2460][orange][somethingelse]

Then in

Tbl_B
good record>> [Name_1][2450][whiskey][otherinfo]
[B]Will Error>>[/B][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):
[IMGnothumb]http://bytes.com/attachments/attachment/6377d1336922937/1tom_table_erro r.jpg[/IMGnothumb]
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!

tbl_b
monkeybutt - 2450 - whiskey
lizardtail - 2450 - whiskey
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

[NeoPa]

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.