Re: Relationship Question

A quick explanation of relationships may help you solve your issue.

Relationships mainly control the adding and deleting of records (you
should uncheck cascade update/deletes).

If you relate a primary table to a secondary table (the primary table
does not have to be the master table and the secondary table may be the
master table) the primary table will contain unique values.

Example: a primary table contains one column called Parts. This column
is unique in the primary table. The master table also has a parts
column, but the column is not unique. For the sake of the examle say
that the master table contains unique records, but the Parts column just
happens to be not unique (but the sum of all the columns in the master
table will make that record unique). If you try to add a parts value to
the master table that is not first contained in the primary table -- you
will get an error message that you are violating referential integrity
and will not be able to add the Parts value to the Master table until
you have first added that value to the primary table.

And for deletes, lets say that you want to delete a parts value from the
primary table because it is obselete. But the master table contains
some records with that Parts value. Again, you will get an error
message that you are violating referential integrity. You wont be able
to delete the Parts value from the primary table until you have deleted
the parts value from the master table.

Note: if the Parts value can be blank in the Master table, then you
need to include a blank value in the Primary table.

If you have checked cascased update and cascade delete, then you can
delete/add whatever you want. But if you delete a parts value from the
primary table by accident and your master table contains 10,000 rows
with that parts value, they will also be gone -- INSTANTLY. Cascade
Updates/Deletes are primarily for Replication operations (that would be
sql server country). I can't think of real good reason to use cascade
update/deletes in Access.

This is relationships in a nutshell.


Rich

*** Sent via Developersdex http://www.developersdex.com ***
.

Relevant Pages

  • Re: Cascade Delete Not Working Correctly?
    ... In order to do this you will need to enable Cascade Update as well as ... > I have a master table that is updated. ... > child records that are referenced by a foreign key with ... > DELETE/INSERT the child. ...
    (microsoft.public.sqlserver.server)
  • Re: setting up a one to many link
    ... "Master List" something like tblCustomers or tblClients would be much ... there may be Transactions that have no matching Master ... to be deleted before establishing an Integrity and Cascade link. ...
    (microsoft.public.access.forms)
  • Re: OTHER BIG BUG : deleting detail master delete
    ... If you have cascade delete on the foreign key relation between your master and detail table in SQLServer you only have to delete and do apply updates for the master dataset. ...
    (borland.public.delphi.database.ado)