Re: Multi-Field Primary Key

Tech-Archive recommends: Fix windows errors by optimizing your registry

It is probably more important for the Inner Join to put an Index on the
ForeignKey [frg_PHONE] in the "Many" Table in this case.

Something troubles me: Do you have Activities that don't involve [PHONE]?

--
HTH
Van T. Dinh
MVP (Access)



"nickh" <nickh@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DBEEC626-99EF-45F1-942A-7FD478282CE4@xxxxxxxxxxxxxxxx
I use the [PHONE] portion only to link in one-to-many to another table that
contains records of activity on that [PHONE]. I am assuming that the
primary
key designation causes a faster lookup during an innerjoin than just an
indexed field? (Is that assumption correct?) I.E., the
[PHONE]+[AUTONUMBER]
would be in the same position in the primary key index as [PHONE] alone,
but
the [PHONE]+[AUTONUMBER] achieves uniqueness whereas [PHONE] alone isn't.
Actually the problem is that a lot of records have a null [PHONE] (because
we
don't have a phone], so then we need the [AUTONUMBER] portion to achieve
uniqueness. So with your ZLS the index would look like
""1
""154
""894
""954
et cetera

whereas the non-null records would have 10-digit [PHONE] + [AUTONUMBER]:
2125551212955
2135551212956
2145551212957
et cetera

Maybe my logic is just totally flawed here. I always learned in db class
that the primary key is faster than just indexing alone, so you always
want
to try to create a primary key to maximize the speed of lookups and joins.




.

Relevant Pages

  • Re: Query troubles
    ... I also don't know what an inner join is. ... If you leave these instructions out of a query, ... PriceID -- Primary Key ... ItemID between the two tables. ...
    (microsoft.public.access.queries)
  • RE: Renumbering Primary Keys From an Imported Database
    ... When I run the query it tells me that I will be updating 1340 rows (However ... Add a new CompanyID autonumber column to the Companies table ... table and define it as the primary key. ... UPDATE Individuals INNER JOIN Companies ...
    (microsoft.public.access.gettingstarted)
  • Re: DTS SQL select issue
    ... You did not say what the primary key on the target table is. ... I am pretty sure that your second query was not doing what you ... INNER JOIN INVENTDIM i ON d.INVENTDIMID = i.INVENTDIMID ...
    (microsoft.public.sqlserver.dts)
  • RE: Multiple records showing when Multiples do not exist
    ... ScannAction.ScanActionID as primary key but I beleive I do have the ... Is Time a date/time field or text? ... Many-to-Many relationship leading to a Cartesian product. ... FROM AccountAction INNER JOIN (ScannAction INNER JOIN (Employees RIGHT JOIN ...
    (microsoft.public.access.queries)
  • DTS SQL select issue
    ... INVENTDIM AS i ON d.INVENTDIMID = i.INVENTDIMID INNER JOIN ... when the task is run I get an error "Violation of Primary Key constraint 'PK_PRICE' Cannot insert duplicate key in object 'PRICE'". ... I feel this is occuring because the selected list contains more then just the primary key fields of the target table. ...
    (microsoft.public.sqlserver.dts)