Hi, I'm looking into porting an application that uses SQL Server as the
database to
one that uses a local Access 2007 database. I am new to Access so excuse
the
dumb questions. I am confused about how to define a foreign key. In SQL
this is
defined as follows:
UserID varChar(64) NOT NULL FOREIGN KEY REFERENCES Users(UserID),
Keep in mind if you're using SQL server, then really your questions on MS
access don't really apply all at all. The behavior of your database be it
C++, VB6, a web server, or MS access connecting to SQL database will not
change how things work one bit at all...
So I set up a table in Access called "Users" with a primary key of
"UserID". Then
in my detail table, I have another UserID field, but there is already
another primary
key.
Why would you be surprised by the fact that the table has a primary key?
while there's absolutely no need for this child table to have a primary key,
in virtually all database systems it is a really really really a good idea
to have unique identifier for each row of any given table that you have. The
fact that this child table has a primary key has absolutely zero bearing,
and no relationship (pun intended) on the fact that this table has a plain
Jane a long data type field that is to be used as a foreign key. As far as I
know be it in oracle, SQL server, or in this case jet, the foreign key
field is a plain Jane regular data type field with a value simply shoved
into it. The fact that a field is a foreign key is in fact only a byproduct
of you the designer deciding it to make it as such. The dataengine, or that
field of itself has no particular special meaning in that it's just simply a
data field that holds a value that is the primary key of reocrd in a table
we are a child of.
Am I supposed to make this an indexed field?
Should the foreign key be an index field? I believe that the table designer
will actually set an index on this field for you. So, if there i not a
index, it should be indexed.
I did go into the Relationships
dialog and set up a one to many relationship between the 2 fields, but it
seems peculiar
that I didn't have to define my second UserID field as a foreign key
somehow.
As long as you drew the join lines correctly in the relationships window,
then the relationship will be setup correclty, and a index will be crreated
for you...
this really work, i.e. will Access allow me to select on UserID from the
detail table
without doing a sequential search?
If there's a index setup, then a sequential scan will not occur. Note that
you don't need anything like a relationship to have this indexing occur, but
just a good idea when you are working with relationships. As mentioned a
foreign key field is really just a plain Jane field, and it should be noted
that it is the user interface or application code that's going to set the
value of this foreign key for you, not the database system.
However, probably more imporant here is what kind of join do you need?
A left join means that a query will return the "parent" records when the
child table HAS NO correspond record.
So, if we have Customers, and Invoices tables, a left join would give us:
CustomerName InvoiceNumber
AppleBee
Donought Shop 1234
Doughnut Shop 1344
Note how AppleBee does NOT yet have a invoice number in the invoices
table..but the query still returns the record. You have to use left joins
for lookup values when you drop in many tables (can't use standard joins in
this case).
So, with a left join, the corresponding child record DOES NOT have to exist.
Just think of "left" side can exist...but the right side does NOT have to !
A middle join, or so called inner join is the standard join, and BOTH tables
have to have a value for the join. The above would produce:
CustomerName InvoiceNumber
Dounought Shop 1234
Doughutn Ship 1344
So, in the above inner join, our customer name of Applebee does not show,
since that customer does NOT yet have a invoice record in the invoice table.
To make a left join, you drop in the tables (in the query builder, or the
relationship designer), and draw the join line to the appropriate filed
between each table. You then double click on the join line. You then click
on the join type button
You get three options:
Only include rows where the joined fields from both tables are equal
(this standard default inner join)
Include ALL records from "Customers" and only those records from
"Invoices" where the joined fields are equal
(this is our left join. So, our main table Customers will be returned in
this query, REGARDLESS if the child records (invoices in this example)
exist, or not!. This is left join
Include ALL records from "Invoices" and only those records from
"Customers" where the joined fields are equal
This sis obviously a right join....
For forms, and sub-forms, and related tables, left joins are quite
important.
If you look at the following screen shot, you can see that most relations
ships are this left join, and RI is enforced.
http://www.members.shaw.ca/AlbertKallal/Articles/PickSql/Appendex2.html
tblBgroup (booking group) for example may, or may not have payments made
(tblPayments). Thus, you can add a booking group, and NOT have to add child
records. However, full RI is enforced, and you can see the side ways 8
"omega" sign AND THE ARROW HEAD. The simple lookup fields are simply just a
arrow drawn, and no "1", or omega sign exists (tblPayments to tblHowpaid for
example is a simple lookup). It is GREAT that I can look at the ER diagram,
and instantly know if child records are required, or they are not!!
The tables that MUST have a child records can also clearly be seen. If you
go from the tblBgroup to the its parent table, you will see table
tblBooking. You can easily see that there is a 1 to many here also, but NO
ARROW head exists. Thus, when I create a booking, my designs will ALWAYS
ASSUME that a child records in tblBgroup (booking group) will exist (ie: I
must code, and assume that when I add a tblBooking records, my code also
assumes that a tblBGroup will also have to be added). In plain English this
means that when I make a booking (reservation), my code assumes that
you MUST have people in that booking. However, I most certainly allow
people to be booked, but not yet have made any payments. So, your
relationship(s) if done right should reflect the rules you as a developer
want to maintain. I should point out that a left join, or a standard
(inner join) both allow child records to NOT exist, but you still
should correctly set this relationship, since when it comes to making
reports, and writing code...I will know what my assumptions
were at the time (ie: do I HAVE to add those child records
for the software to function correctly. So, if I write code to
make a booking, all of my code thus assumes that people
are also to be added to the booking. Break that assuming
of mine, and likely my code will break).
So, the ER diagram can convey a lot about your designs. Down the road, I can
now look at that diagram, and when writing code, I will know if the design
can, and does assume if child records are required. If you look at that
table, it is VERY RARE that I require the child record. That application has
about 60 tables, and I think only 1 or 2 in the whole thing is NOT a left
join. Hence, you most certainly should set the relation in the window for
future reference, and also it will help you when you create a query, or a
report.
