Where to define Relationships - FE or BE?

[ThomasAJ]

Where is it best to define database Relationships.

I have an old split database that has some relationships in the Back End but
also others in the Front End. I presume it should be the BE and that the FE
ones have no effect or do they?

 

[Rick Brandt]

Where is it best to define database Relationships.

I have an old split database that has some relationships in the Back
End but also others in the Front End. I presume it should be the BE
and that the FE ones have no effect or do they?

All relationships in the front end do is create default join lines in new
queries (and perhaps documentation).

 

[Tom van Stiphout]

On Fri, 24 Oct 2008 17:01:01 -0700, ThomasAJ

BE.
And you need to enforce them.

-Tom.
Microsoft Access MVP

 

[ThomasAJ]

Are you implying that BE relationships do not?

 

[ThomasAJ]

Can you please expand on "need to enforce them" just a little.

 

[Rick Brandt]

Are you implying that BE relationships do not?

Nope

 

[John W. Vinson]

Where is it best to define database Relationships.

I have an old split database that has some relationships in the Back End but
also others in the Front End. I presume it should be the BE and that the FE
ones have no effect or do they?

The relationships can only exist and be enforced in the backend; that's where
the tables are. Relationships established in a DIFFERENT database (a frontend)
would have no way of being enforced, since someone could open the backend
directly, or from a different frontend.

So: relationships are *only* in the backend. Anything in the frontend is just
for documentation or information, not for controlling the data.

 

[tina]

relational joins that are not enforced are basically cosmetic - they do
nothing to control or protect the table data. open your database to the
database window, then open the Relationships window. double click on any
"line" connecting fields in two tables, to open the Edit Relationships
dialog. the left column shows the "parent" table and its' primary key (the
"left side" of the relationship); the right column shows the "child" table
and its' foreign key that corresponds to the primary key in the parent table
(the "right side" of the relationship). underneath are three options:

Enforce Referential Integrity
(which should always be checkmarked)

Cascade Update Related Fields
(which is usually only necessary when the primary/foreign key field(s) hold
data that has "real world" meaning which may be changed by the user)

Cascade Delete Related Records
(which is useful for getting rid of child records automatically when the
related parent record is deleted, BUT it should be activated **only after
careful thought to the ramifications in each specific situation** because
automatically deleted records are like all other deleted records - gone
forever)

if you need further information on the reasons for enforcing referential
integrity on relational joins, suggest you read up/more on the principles of
relational design.

hth