Hi,

I have created 2 tables and assigned primary keys to each, however I now want to create foreign keys to link the tables together. How should I go about doing this ?

staff ( name, certified, authority )
- name, certified = PK


staffEmployed ( name, certified, location, yearStart, yearFinish )
- name, certified, yearStart = PK


Now I want to be able to query the staffEmployed table so that data from the staff tables will be linked in automatically. What should I use as the foreign key ?

Thanks for any help...

Chris.

I believe what you are asking is two very closely related, yet slightly different questions.
1) What foreign key should I implement to assure integrity.
2) What join condition would I use to query these two tables correctly.

The principle being that the foreign key you create will most likely contain the columns you want to join on.

The foreign key would be created and used to enforce integrity between the two tables, in this case I believe you would want to make sure that an entry in staffEmployed would have a corresponding entry in staff. This would mean creating a foreign key constraint between the two tables where staffEmployed (name, certified) references staff (name, certified).

I believe the join would also use those same columns:
SELECT
s.name,
s.certified,
s.authority,
e.location,
e.yearStart,
e.yearFinish
FROM
staff s
INNER JOIN
staffEmployed e
ON
s.name = e.name
and s.certified = e.certified ;

I should add a disclaimer that I'm not sure I understand all the business rules and entities represented by these tables, despite that I have three other notes for you:

1) Changing Key Values:
'certified' is part of the primary key in the two tables, will this value ever change (or name for that matter)? While it is not necessarily bad for a value within a primary key to change, you must be sure that all related entries in other tables are updated as well. This can be a pain unless your RDBMS supports cascading updates to keys. Just something to be aware of.

2) Possibility For Duplicates:
Is this for a smaller organization where you are guaranteed to not have any two staff members with the same name and certification? I've worked at companies with a handful of people with the same full name/MI, and even with 2 people that have the exact same name. That said, if they could share the same value for certification you could end up with a duplicate in your primary key, which the RDBMS would of course not allow.

3) Surrogate Key?
If the possibility of duplicates exists (from above), you may want to consider a surrogate key on the staff table. This would of course imply the use of that surrogate key in the staffEmployed table as well. This would be under the assumption that the addition of the authority column to the primary key would not assure uniqueness of the key either, and therefore there is no candidate key in the table (unless you add additional attributes as columns).

I was thinking more about keeping the integrity. So im going to put...

CREATE TABLE staffEmployed (
name VARCHAR2(15) NOT NULL,
certified VARCHAR2(4) NOT NULL,
...
PRIMARY KEY (name, certified, yearStart),
FOREIGN KEY (name, certified) REFERENCES staff(name, certified));

* Certified is the year the member of staff was certified so it will never change. Also duplicate names should not be a problem.

Thanks for the help...