BruceM wrote:
If I use
name and address as the PK, what becomes of relationships if the name and/or
address changes?

If your table only has three attributes (e.g. first_name, last_name,
postal_address) and a composite/concatenation does not provide a key
then you don't have a key. You must expand your data model e.g. collect
more information, create an artificial key and expose it (e.g. assign
it to your contact entities). Adding a 'notes' column into the mix is
probably not going to lead to an acceptable solution to the problem.

If you are going down the road of creating your own encoding system,
you should be able to do better than the autonumber algorithm (i.e.
MAX+1 or MAX+10000 if something could have gone 'wrong'), as you have
done with your

CHECK(EmployeeID LIKE '[1-9][0-9][0-9][0-9]')

constraint (it has 'internal' validation i.e. a value other than four
numeric characters is obviously incorrect).

ALTER TABLE Prospects
ADD CONSRAINT fk_prospects__contacts FOREIGN KEY
(contact_first_name, contact_last_name, contact_postal_address)
REFERENCES Contacts
(first_name, last_name, postal_address)
ON DELETE CASCADE
ON UPDATE CASCADE;

As I alluded to before, you may have problems with the postal address
which may not be captured in a suitable way to be used for in a key.

I would want my PK to be something that will not change.

Permanency is a boon but one doesn't always have that luxury.