Referential integrity

[Lou_Reed]

I am confused. I am trying to install (instill) referential integrity in the database tables. As we all know it will not do it unless everything is the
same in the design page for the same object in the two tables.. If anything is different between the two objects then a error message pops up. However,
in my case everything is the same! y

Yet I still get an error message telling me that they must be the same and it cannot do it.

I am not sure what could be incorrect.

Any thoughts.

Thanks in advance.



Respectfully,

Lou reed

[ItsMe]

Sometimes a referential integrity rule cannot be applied because it will violate an existing constraint.

[Lou_Reed]

Maybe it is best if I upload/post this. There is no data in the record or on it. It is all brand new and I think I can make my point better. Please let me know if it is alright to do so.

Respectfully,

Lou Reed

[orange]

It would be helpful if you told us the subject matter and showed us some table designs.

For more on referential integrity.

[Lou_Reed]

database.zip

Okay, here is the uploaded file, zipped. As you can see I cannot enforce referential integrity on the
following tables

tblOrg, tbllocation and,
tblPersonnel

Any attempt to enforce it fails with a warning message. It seem that the object in the database have exactly
the same values:

they are:

Field Size long integer

Decimal Paces Auto

Required No

Indexed yes, no duplicates

Text Align general


It seems that they are the same and performing tasks such as making them the same worked in all other cases for this relation layout.


It just failed on these.


Any help appreciated. Thanks in advance.

Respectfully,


Lou Reed

[orange]

Did you watch the video by Prof Widom?
Do you have a description of the business that this database design is intended to support?

[Lou_Reed]

I do not have the video by Professor Window. Where may I find it?

The business chart is simply a organization of employees at the R&D lab where I work.

I do not see that this has anything to do with referential integrity. Why can I not enforce it on those database objects that I mentioned above.

Thanks in advance.

Respectfully,

Lou Reed

[orange]

It was the link in my post #4.

[Lou_Reed]

I would not dare watch a video on company time. I am wondering if you could give me a link
to a written summary. That I will print out and read.

Any help appreciated. Thanks in advance.


Respectfully,

Lou Reed

[orange]

Lou,

That's the nice thing about Google / Bing etc.

Type in your question and select a few responses to review. If it doesn't answer your question, then try again. Change the search terms if necessary.

Watch the video at lunch time or after hours if it is against company policy. I'm not sure who you work for, but if database is part of your job function/duties, and you need to know about referential integrity- what are the options? They could send you on course. They could bring in a trainer....
Are they cost conscious? How much would it cost to review that video. Perhaps 3 or 4 people could all watch it at the same time (group learning).

Any way - here are some paper/text based responses (referential integrity and related info):


https://www.princeton.edu/~achaney/t...integrity.html Data Integrity
https://msdn.microsoft.com/en-us/lib...=vs.71%29.aspx Referential Integrity
https://msdn.microsoft.com/en-us/lib...=vs.71%29.aspx Business Rules
http://www.miswebdesign.com/resource...apter-3-5.html Referential Integrity
http://www.odbms.org/wp-content/uplo...ember-2005.pdf Importance of referential integrity

Good luck.

[Lou_Reed]

I see what the links defined as referential integrity, but I do not think it applies to my case. The definition of referential integrity

"is that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity insures ensures that
the relationship between two tables remains synchronized during updates and deletes."

That I what I thought that it meant; there is no data in my current database. The tables are empty. The database is being built by me from an older
one that one of our interns worked on last summer. The tables are empty.

That is why I cannot understand this error.

I am sure the objects in the two databases are the same (same parameters), but I cannot get them past referential integrity.

If you look at the example that I posted you will see where the failure to enforce referential integrity occurs.

Please examine the posted db.

Thanks in advance.


Respectfully,
Lou Reed

[orange]

What specifically are the Business rules in your "application"?
From Post 6

Do you have a description of the business that this database design is intended to support?

The database by itself tells us nothing about your business.


ooooooooooo----------ooooooooo------------ooooooooooo

Consider this set up where there are a number of People, and a number of Hobbies. We have a database to record information about People and the Hobbies they have. A Person may have 0,1 or more Hobbies. A Hobby is from a list where a hobby may be participated in by 0,1 or many Persons. In this case John Doe has 2 hobbies, Sheza Payne has 1 hobby. Kody Akbair has no hobbies. Nobody in tblPerson participates in Snow Skiing. (These are the business rules that determine relationships)

tblPerson
PersonId PK
personFName
personLName
other person info
-data-
1 John Doe
2 Kody Akbair
3 Sheza Payne

tblHobby
HobbyId PK
HobbyName

-data-
1 Snow Skiing
2 TiddleyWinks
3 Video Games

tblPersonHasHobby
HobbyId PK FK
PersonId PK FK


-data-
1, 3 John Doe Video Games
1, 2 John Doe TiddleyWinks
3, 3 Sheza Payne Video Games

In tblPersonHasHobby, the primary key is a composite Primary key where both HobbyId and PersonID are required to identify records uniquely. Each field is also a foreign key to records in the respective tables.

If John Doe is deleted from the tblPerson you must remove his record(s) from tblPersonHasHobby
in order to preserve referential integrity. If you leave his records in tblPersonHasHobby, then the Foreign key in tblPersonHasHobby which points to tblPerson would be pointing to a table with no valid row with a Primary key to match the foreign key.

Which is exactly what you said

"is that the foreign key in any referencing table must always refer to a valid row in the referenced table. Referential integrity insures ensures that
the relationship between two tables remains synchronized during updates and deletes."

[Lou_Reed]

From an earlier post

[code]
The business chart is simply a organization of employees at the R&D lab where I work.
[code/]

That is what I put in a earlier post.

As I am sure you know I worked many Access tutorials during the last four months. I am
not sure where I ever put in a business rule. Please explain.

Give me a link for it.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

Respectfully,

Lou Reed

[orange]

Lou,

I gave you a link (third link in post 10)
https://msdn.microsoft.com/en-us/lib...=vs.71%29.aspx
but here is the article:

Business Rules for Data Access

Visual Studio .NET 2003

You can use business rules to provide correct and consistent control of your application's data access. Even better, you should expect other applications to use the same business rules, and thereby benefit from the built-in process dependencies and relationships you have provided. In general, business rules that handle data access must be designed thoughtfully to provide self-contained, carefully coordinated processes.
Choosing How to Enforce the Business Rule

In defining business rules for data access, you choose which run-time mechanisms your application will use to enforce the rules that apply to your data. In essence, you must determine which service or component should enforce the rule and exactly how the rule will be implemented. In making this determination, you should consider several questions:

• What enforcement mechanisms are available? Rules can be enforced by the DBMS (database management system), by code in application services, or by user-interface artifacts such as Windows Forms or Web Forms. Within the DBMS, you can enforce a rule with data types, constraints, triggers, or stored procedures. Within a component such as a business object, you can enforce a rule programmatically through event handling (such as enforcing a rule each time the RowChanged event is fired). Within a user-interface component, you can enforce a rule programmatically, or through the user-interface controls that can handle local data editing.
• Is redundant enforcement desirable? Although redundant enforcement of a rule might seem wasteful, you should not rule it out. For example, you might choose to enforce a rule both on a form and in the DBMS. By enforcing the rule in the form, you improve the performance of the data-input operation (by avoiding round-trip communication with the database). By enforcing the rule in the DBMS, you ensure that all data conforms to the rule — not just data entered through that form. In a vibrant, fast-paced development environment, it is possible that another developer might create a form that does not enforce the rule. It is also possible that an expert, highly privileged database user might use SQL to insert rows directly into the database. In such an environment, the most reliable way to assure the correctness of the data is to use the DBMS to enforce the rule. ]
• How aggressively should the rule be enforced? That is, does the rule require aggressive, constant enforcement, or is it acceptable to enforce the rule only periodically or only during certain phases of a process? For example, a rule requiring that "each credit card number be 16 digits" might deserve aggressive enforcement because the database should never contain an ill-formed credit card number. On the other hand, a rule requiring that "each purchase order must have a shipping address" might not need to be enforced continuously. Such a rule could deserve enforcement only during certain times during order processing. For example, if the sales specialist and the customer spend several days refining a purchase order while the customer chooses exactly which products to buy, the purchase order can exist in the database in an incomplete state during those days. But when the purchase order is finalized and transmitted to the shipping department, it must have a shipping address. The rule requiring a shipping address does not need to be enforced during the preliminary stages of the ordering process.
• How will enforcement affect performance? The type of rule enforcement you choose can affect the user perception of your application's performance. For example, if a rule can be enforced simply on a form or in the database, you can choose to enforce it on the form so that the users do not have to wait for a round-trip communication with the database before discovering a typographical error.[/B][/B][/B][/B]
• How will enforcement affect maintainability? If a rule is especially intricate, you will want to implement it in a way that makes it easy to maintain. That is, you will want to choose an implementation mechanism that exhibits a strong affinity with the rule itself. For example, if the rule controls the sequencing of automated tasks within your software solution, you might want to implement it in a language that has mechanisms for complex CASE or SWITCH statements, and in a language the has mechanisms for firing and responding to events.

Determining Business Rules

Your application requires data access business rules for any of the following circumstances:

• Inserting, updating, deleting, and viewing data.
• Validating data.
• Controlling data security.
• Handling multifile data access.
• Providing referential integrity.

Data Manipulation

You can use a business rule each time your application inserts, updates, deletes, or views data. Business rules implemented this way provide concise control over the data that can be updated and how it is updated. For example, if your application applies new sales orders to the invoice file, a business rule should automatically check the customer's credit limit before accepting and inserting the sales order line items.
Validating Data

Data validation is the process of verifying field values (is the numeric field really numeric and within range?) and validating related file values (does the publisher identification exist in the Publishers file?). By putting all of the data validation routines into business rules, your application can guarantee correct data and easily adapt to future requirements. For more information, see Data Validation.
Controlling Data Security

Your application may require access security to control who gets to view and modify your application's data. Security concerns more than just authorizing user logons: security is about controlling access to all of your application's architectural components and data access processes, including:

• User interface services.
• Operating system services.
• Business process services.
• Data transmission services.
• Database services.

For more information on securing your application from unauthorized tampering or modification, see Securability.
Handling Multifile Data Access

If your application needs to trace a complex chain of logic and data values as preparation for a decision process, you should use a business rule to simplify the multifile access. Your business rule would automatically find all of the required data structures and repackage them for easy use. For example, suppose your application needs to determine the maximum possible payout for a single procedure in a multiline health care claim. While inspecting the current line item, the beneficiary's entire claim history must be searched for prior use of the identical procedure. Additionally, lifetime and current year-to-date limits must be checked to determine the allowable amount. This kind of multifile data access presents an excellent opportunity to create a reusable business rule that consistently and correctly handles the situation.
Providing Referential Integrity

One of the most common uses for business rules is handling referential integrity. The issue of referential integrity is important for relational as well as indexed files. Because indexed files (such as VSAM) are typically just raw data storage engines, your application must provide custom code to handle constraints, foreign key deletions, and other common referential integrity issues. Application-based referential integrity can also be appropriate for relational databases, especially in situations where the available triggers, constraints, and stored procedures are either inadequate or too complicated. For more information on maintaining accurate table relationships, see Referential Integrity.
Creating data access business rules requires careful planning, especially when considering interaction with existing business rules belonging to other applications. The benefit is that your data is secure, accurate, and easily accessible by not only your application but by other applications as well.

[orange]

Lou,
I looked at your database you had data in the tblPersonnel that wasn't in tblOrg or tblLocation.

from your post 11

That I what I thought that it meant; there is no data in my current database. The tables are empty. The database is being built by me from an older
one that one of our interns worked on last summer. The tables are empty.

You had a record in tblPersonnel with 1 record.
1 Oldford Michael Phone:8396 OrgID:2544 LocID:2117

there were no records in tblOrg or tblLocation.
I removed the field values Orgid 2544 and LocID 2117 and edited the relationships to have referential integrity. Worked without issue.

Try it in your database. Let us know.