how do u assign a foreign key?

[D L Barnard]

how do i design a row in a table as a foreign key. i know what a foregn key
means, yet i cant remember how to assign one. thank you. (university
assignment).

 

[John Vinson]

how do i design a row in a table as a foreign key. i know what a foregn key
means, yet i cant remember how to assign one. thank you. (university
assignment).

There is nothing special within a table defining a field as a foreign
key; a field becomes a foreign key when it is *used* as a foreign key,
i.e. when you create a relationship between two tables joining the
Primary Key field (or, in fact, any other field which has a unique
index) in the "one" side table to a field of the same datatype in the
"many" side table. This field is thereby being used as a foreign key.

When you create such a relationship in the Relationships window,
Access automatically creates a nonunique index on the foreign-key
field; but the index merely helps Access enforce the relationship, it
does not define it.

Depending on your definitions (and some will likely disagree with me
here!) a field can become a foreign key without defining any
relationship at all - simply create a Query linking TableA.FieldX to
TableB.FieldY means that FieldY is a Foreign Key (for the purpose of
this query).

John W. Vinson[MVP]

 

[Chris2]

how do i design a row in a table as a foreign key. i know what a foregn key
means, yet i cant remember how to assign one. thank you. (university
assignment).

D L Barnard,

Foreign Keys are not created for rows, they are created for columns.

Foreign Keys define "relationships" specified in a "data model".

Or, put another way:

"Foreign keys enforce referential integrity by completing an
association between two entities."

In MS Access, you design them either by using the Relationships Window
and dragging fields between tables shown on the window; or you use
Data Definition queries (which appear in the database windows query
tabe with a little icon of a drawing triangle, pen, and ruler) and
write DDL SQL (Data Definition Language).

Here's a quick example of using DDL:

CREATE TABLE Policies
(policy_id AUTOINCREMENT
,policy_start_date DATETIME
,policy_renewal_date DATETIME
,premium_payable CURRENCY
,other_policy_details TEXT(255)
,CONSTRAINT pk_Policies PRIMARY KEY (policy_id)
)

CREATE TABLE Life
(life_id AUTOINCREMENT
,policy_id LONG NOT NULL
,occupation_code TEXT(255)
,life_expectancy INTEGER
,CONSTRAINT pk_Life PRIMARY KEY (life_id)
,CONSTRAINT fk_Life_Policies
FOREIGN KEY (policy_id)
REFERENCES Policies (policy_id)
)

The first CONSTRAINT clause in this DDL specifies each Table's Primary
Key. The second CONSTRAINT clause in the Life Table specifies that a
Foreign Key relationship exists between Life and Policies based on the
columns specified.


Here's a handy reference to some definitions:
http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html.


Sincerely,

Chris O.