Assign Foreign key

[Guest]

Hi
My understanding about foreign keys is that they are generally a primary key
in foreign table. The primary table primary key would be unique. In fact I
believe all keys should be unique????

The problem i am having is I have two tables

ChemicalsTbl
symbol - Text 8 Bytes long (primary key)
name - Text 25 Bytes

ReadingsTbl
symbol - Text 8 Bytes long (foreign key)
Readdate - Date/Time Default value today
Read1 - Decimal - Precision 10 - scale 3 - Decimal 3
Read2 "
Read3 "
Read4"

Next... After inputing data into both tables I have duplicates in
ReadingsTbl.symbol (foreign key)

Therefore I am unable to ensure referential integrity.

My question is how do I get around this?

I have tinkered with indexing and not sure where else to look.

If anyone could help I would be greatful.

Regards
Tanya

 

[Allen Browne]

Tanya, it sounds like you are trying to set up a one-to-many relationship
between ChemiclesTbl and ReadingsTbl, i.e. one Chemical can have many
Readings.

ReadingsTbl should have its own primary key field - typically an autonumber.
The symbol field cannot be primary key in this table, since it can occur
many times.

To create the relationship betweeen the 2 tables, open the Relationships
window (Tools menu, or Database Tools tab of ribbon if you use A2007.)
Define the relationship by dropping ChemicalsTbl.symbol onto
ReadingsTbl.symbol, and check the box for Referential Integrity (so there
can never be a reading for a symbol that doesn't exist.)

In summary, a foreign key is not a primary key. It is a field in one table
that links to the primary key of another table, but it is not a primary key
in its own table (except in a one-to-one relation, which is rare.)

 

[Guest]

Thank you Allen

Tanya, it sounds like you are trying to set up a one-to-many relationship
between ChemiclesTbl and ReadingsTbl, i.e. one Chemical can have many
Readings.

ReadingsTbl should have its own primary key field - typically an autonumber.
The symbol field cannot be primary key in this table, since it can occur
many times.

To create the relationship betweeen the 2 tables, open the Relationships
window (Tools menu, or Database Tools tab of ribbon if you use A2007.)
Define the relationship by dropping ChemicalsTbl.symbol onto
ReadingsTbl.symbol, and check the box for Referential Integrity (so there
can never be a reading for a symbol that doesn't exist.)

In summary, a foreign key is not a primary key. It is a field in one table
that links to the primary key of another table, but it is not a primary key
in its own table (except in a one-to-one relation, which is rare.)

 

[Jamie Collins]

ReadingsTbl should have its own primary key field - typically an autonumber.
The symbol field cannot be primary key in this table, since it can occur
many times.

I think the most likely candidate is a compound (no pun intended) key
of (symbol, Readdate). A key should prevent duplicate data; just
adding an autonumber would allow duplicates to be created e.g.

INSERT INTO ReadingsTbl (symbol, Read1) VALUES ('FE'. 1.1)
;
INSERT INTO ReadingsTbl (symbol, Read1) VALUES ('FE'. 1.1)
;
INSERT INTO ReadingsTbl (symbol, Read1) VALUES ('FE'. 1.1)
;
etc

In summary, a foreign key is ... a field in one table
that links to the primary key of another table

No, the OP has the point: *generally* it is the 'primary key' however
RI can be enforced using any 'unique' constraint (a.k.a. key).

a one-to-one relation, which is rare.

Not at all. The classic example is human relationships where in
marriage it is polygamy (one to many) that is the rare case and
monogamy (one to one) that by far the most common across cultures.

Jamie.

--