How to Enforce Referential Integrity on Access Relationships
Posted on
6,921 Points
3:26
crystal (strive4peace) - Microsoft MVP, Access
Love empowering people by teaching and helping them develop applications.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other.
As you build tables in Microsoft Access, or link to tables that are in SQL Server, Excel range, or other database, put them on the Relationships Diagram
Stretch or shrink fieldlists so everything shows
To create a relationship in Access, click on the Primary Key in the main table, drag to the Foreign Key in the related table, and let go.
In the dialog box that pops up,
check: Enforce Referential Integrity
This is important so that there are not values that don't match up to anything.
Error creating relationship?
If there is bad data, a relationship with referential integrity cannot be created. Fix the bad data and try again.
If the table is linked, you can show it on the diagram but you cannot create a real relationship that is not already there. If relationships are not defined, you can drag lines without enforcing referential integrity to show what is supposed to match.
Define relationships and arrange the diagram as you build your tables.
Enforce Referencial Integrity unless you have a specific reason not to.
have an awesome day,
crystal
~ connect to me, let's build it together
http://www.msAccessGurus.com
As you build tables in Microsoft Access, or link to tables that are in SQL Server, Excel range, or other database, put them on the Relationships Diagram
Stretch or shrink fieldlists so everything shows
To create a relationship in Access, click on the Primary Key in the main table, drag to the Foreign Key in the related table, and let go.
In the dialog box that pops up,
check: Enforce Referential Integrity
This is important so that there are not values that don't match up to anything.
Error creating relationship?
If there is bad data, a relationship with referential integrity cannot be created. Fix the bad data and try again.
If the table is linked, you can show it on the diagram but you cannot create a real relationship that is not already there. If relationships are not defined, you can drag lines without enforcing referential integrity to show what is supposed to match.
Define relationships and arrange the diagram as you build your tables.
Enforce Referencial Integrity unless you have a specific reason not to.
have an awesome day,
crystal
~ connect to me, let's build it together
http://www.msAccessGurus.com
Video Steps
1. To create a relationship in Access, open the Relationships Diagram
2. click on the Primary Key in the main table
3. drag to the Foreign Key in the related table
4. and let go of the mouse.
5. When the dialog box pops up, check: Enforce Referential Integrity
6. and click Create
7. Rearrange the Relationships Diagram so everything shows and flows
0 Comments
Suggested Videos
This article will explain how to deal the following error in Outlook:
Exchange 2013 Troubleshooting: Error 0x8004010F - The operation failed. An object cannot be found
Today we will look at a very common debate while developing the data access layer. This is to decide if we want to use stored procedures and keep our logic in the database or do we want to use an ORM like Entity Framework and keep all logic in our m…
Suggested Courses
Next Video:Query data with no SQL code (10)