Kelvin:
I think you are confusing queries with relationships. Tables in a query can
be joined in 3 ways, which the options in the 'Join Properties' dialogue
represent:
1. An INNER JOIN. This is the default when you create a query and returns
only those rows where there are matching values for the join fields in both
tables.
2. A LEFT OUTER JOIN. This returns all rows from the table on the left
side of the join (subject to any criteria which restrict this), and only
matching rows from the table on the right side (note that you can't have any
criteria on the right side table as this would in effect make it an INNERE
JOIN). Any fields from the table on the right side for rows where there is
no match with a row on the left side will be Null.
3. A RIGHT OUTER JOIN. As you'd expect this is simply the reverse of the
above, returning all rows from the table on the right side of the join, and
only matching rows from the table on the left side.
In the relationships window on the other hand if you right click on the join
line between two tables and then open the 'Edit Relationship' dialogue, this
includes a 'Join Type' button which opens the same dialogue as above. What
this does is set the default join type when you join the tables in a query to
whatever you select here. The options in the main 'Edit Relationship'
dialogue are threefold:
1. The 'Enforce Referential Integrity' checkbox creates a 'constraint'
which means that a row can only be inserted into the referencing table (many
side) where a matching row already exists in the referenced table (one side).
Say you have a table States and a table Cities, you can't insert a row in
Cities for San Francisco with a value CA in its State foreign key column
until a row for California with a key value CA has been inserted in States.
Conversely you can't delete a row from States if a row exists in Cities with
a matching State value. Enforcing referential integrity where its
appropriate is very important.
2. Cascade Deletes. This means that if you delete a row from a referenced
table (one side) all matching rows in any referencing tables will
automatically be deleted. USE THIS CAREFULLY! You would probably not want
to use it in a States--<Cities relationship for instance, but might well do
so in a Customers--<Contacts relationship, as if you delete a company from
your customers table there is no point retaining the records of contacts in
that company.
3. Cascade Updates. This means that if you change the value of a primary
key column in a row in a referenced table the values in any foreign key
columns in matching rows in referencing tables will automatically change. If
California for some reason changed from CA to CF then the State value in
Cities for San Francisco, Los Angeles, San Diego etc would also change. if
the primary key is an autonumber column StateID and the foreign key a
corresponding long integer number column, there is no point enforcing cascade
updates as the value of an autonumber can't be changed.
Relationships in the front end merely set the defaults for queries. All
constraints should be applied in the back end. I don't use SQL SERVER
myself, but in standard SQL this is done with the DDL 'ALTER TABLE ADD
CONSTRAINT' command on a existing tables, or as a 'CONSTRAINT <constraint
name> REFERENCES <referenced table (referenced column)>' specification when a
referencing table is created with DDL.
Ken Sheridan
Stafford, England