Integrity (1 Viewer)

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 19, 2002
Messages
42,970
At the moment if I change the stock number in the stock table, I lose all the Order Detail information in the Order Detail Table. Of course what I would like to happen is that I change the stock number in the stock table and all the stock numbers change in the Order Detail Table.
I did tell you how to solve this problem (Cascade Update) and also that you should not use this type of changeable primary key. The other experts have also suggested that you use an autonumber primary key.

An example of how this helps you is if you want to change the "part Number" of a part, you just change it! NOTHING else changes because although there is a unique index on the part number to prevent adding duplicates, this is a human identifier and is not used for anything in the application except for searching and display.

Minty suggested a method of changing the primary key. There is no need to do that when all you need to do is to change the relationship to Cascade Update. However, if you are willing to take the time to change the PK (and it will not be an easy task at this time) then we can help you do it. Cascade Update will not help in this situation. It will have to be done all with update queries and once it's done, you would remove RI from the original fields and add it to the New, numeric PK and FK, and the final step would be to remove the old text PK and FK's.

People have a comfort level when they look at a code value and "know" what it is. In some cases I even develop these human readable codes but the reality is that the application does not care and you should not make it care by making these "human" codes a primary key. Surrogate keys (which is what autonumbers are) stand in for the unique identifier that the business prefers to see. The candidate key (what we call the unique business identifier) is almost always just a data field with a unique index to prevent duplicates but the surrogate key (autonumber) is used for all relationships with child tables. Users are given search options that will search their "human" key because they will probably never see nor do they have to see, the surrogate key that ties everything together behind the scenes.
 
Last edited:

Workflow

Registered User.
Local time
Today, 05:32
Joined
Oct 31, 2014
Messages
31
I did tell you how to solve this problem (Cascade Update) and also that you should not use this type of changeable primary key. The other experts have also suggested that you use an autonumber primary key.

An example of how this helps you is if you want to change the "part Number" of a part, you just change it! NOTHING else changes because although there is a unique index on the part number to prevent adding duplicates, this is a human identifier and is not used for anything in the application except for searching and display.

Minty suggested a method of changing the primary key. There is no need to do that when all you need to do is to change the relationship to Cascade Update. However, if you are willing to take the time to change the PK (and it will not be an easy task at this time) then we can help you do it. Cascade Update will not help in this situation. It will have to be done all with update queries and once it's done, you would remove RI from the original fields and add it to the New, numeric PK and FK, and the final step would be to remove the old text PK and FK's.

People have a comfort level when they look at a code value and "know" what it is. In some cases I even develop these human readable codes but the reality is that the application does not care and you should not make it care by making these "human" codes a primary key. Surrogate keys (which is what autonumbers are) stand in for the unique identifier that the business prefers to see. The candidate key (what we call the unique business identifier) is almost always just a data field with a unique index to prevent duplicates but the surrogate key (autonumber) is used for all relationships with child tables. Users are given search options that will search their "human" key because they will probably never see nor do they have to see, the surrogate key that ties everything together behind the scenes.

I did thank you for your post and also replied to it, saying "it all went above my head" meaning I didnt understand it, sorry
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 19, 2002
Messages
42,970
OK, now that you have seen the error, do you understand what I said? We're trying to help. I try not to talk down to people when I provide answers because I rarely know what the actual experience level is for the poster. That means that sometimes the whole answer just flies over the poster's head. If you don't understand something I post, please ask for an explanation. Please try setting Cascade Update to yes and see if that solves the problem.
 
Last edited:

Workflow

Registered User.
Local time
Today, 05:32
Joined
Oct 31, 2014
Messages
31
OK, now that you have seen the error, do you understand what I said? We're trying to help. I try not to talk down to people when I provide answers because I rarely know what the actual experience level is for the poster. That means that sometimes the whole answer just flies over the poster's head. If you don't understand something I post, please ask for an explanation. Now that you know that simply adding a control to the form isn't the solution, you can try setting Cascade Update to yes and see if that solves the problem.

I cannot change the intergrity in the "join" because I get an error, something like "referential integrity enforced and it wont let you change a number because there are related records in another table"

It would appear there is a record for every stock number in every order giving some 2.7billion records :(

I might give up on this again, :( thanks for all your help anyway
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:32
Joined
Feb 28, 2001
Messages
26,996
The very first thing you should do is study "database normalization." This will help you in designing where you need to go. But let me make a comment or two from your explanation.

I might have to use a stock number that was used previously by something else.

No. You CANNOT allow this to happen. However, let me do some quick inferential analysis. You wouldn't have said this unless you were running out of room in the field that holds stock numbers. Which means you have a small stock number and a lot of stock. The positive spin on this is that you have business growth. (Congratulations.) And now you are seeing what is commonly called "growing pains." The CORRECT way to do this is to expand the size of the stock number field. However, if you have RI enabled, you can't do this so easily.

I am betting that you are "under the gun" to get this done sooner than yesterday, but you have a technical limitation and a major rewrite ahead of you. It is time to consider that your original efforts were made from a migratory perspective (from Lotus) but the new reality of growth means it is time to consider a new set of structures.

I am toying with the idea of starting all over again and then export/import the data.

I now want to change my stock numbers. If I change the stock number it doesnt change the order form.

In fact, the best way to maintain the relationships among your data would be to redesign your structures and then do an export/import sequence into tables where your fields that were restricting you in the older design have more room for more variations.

Some other things you can do in the forum involve its Search function. When you are at the top of a forum page, there is a long blue ribbon just underneath the section that shows your login ID and the topic on which you are focused. On that ribbon, 3rd from the right, is the Search option. Look up some topics here, do some reading, and see how folks approach these kinds of problems.

Look up: "Natural Keys" and "Surrogate keys" to see some long-winded discussions on what each one is and why you would choose one over the other.

Look up: "Business Model" to see about design issues in terms of making your database match the reality of your business. From what I see, you are perilously close to a situation in which "the tail will wag the dog." That is, you are close to having the limitations of your database constrain your business decisions.

Look up: "Database normalization" for topics on how to design normalized tables. These normalized tables will help you where you have things that depend on other things, or things that describe the details of other things.

Look up: "Inventory control" - since a large part of your problem seems to be how to manage your burgeoning stock of things to sell.

Here is the question you have to ask yourself. Look at it from this viewpoint. When you have a used car that starts to need more expensive maintenance, and the frequency of that need starts to increase, at what point do you break down and buy a new one?

The computer equivalent here is, at what point will the idea to continually patch up and tweak a complex and (by your own admission) poorly designed database no longer become cost effective? You are facing the back half of that old adage: If you don't have time to do it right the first time, how will you EVER find time to do it again?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 19, 2002
Messages
42,970
Everything that Doc said is good advice. Having primary keys that can change is definitely poor practice. But ---- here is a picture of where you need to set cascade update. Check the box. Then when you change the value of the PK in the parent table from "abc" to "xyz" all the child records will be updated as well.

Just because you can do something doesn't mean that you should. Setting Cascade Update to true will allow you to change the values in the parent record. Once they have been changed, you should definitely go back and uncheck the box. The better solution of course is to use autonumbers but that may be a bridge too far at this juncture.
 

Attachments

  • CascadeUpdate.JPG
    CascadeUpdate.JPG
    73.7 KB · Views: 82

Workflow

Registered User.
Local time
Today, 05:32
Joined
Oct 31, 2014
Messages
31
Everything that Doc said is good advice. Having primary keys that can change is definitely poor practice. But ---- here is a picture of where you need to set cascade update. Check the box. Then when you change the value of the PK in the parent table from "abc" to "xyz" all the child records will be updated as well.

Just because you can do something doesn't mean that you should. Setting Cascade Update to true will allow you to change the values in the parent record. Once they have been changed, you should definitely go back and uncheck the box. The better solution of course is to use autonumbers but that may be a bridge too far at this juncture.

Just to reiterate, its not a Primary Key and its not a autonumber, I cannot change to cascade update because it states there is a error, about having records.

I cannot and do not wish to use autonumbers, my stock numbers are set at 5 numbers, the first 2 will identify what the product category is, so anything starting with 80xxx will be textiles, 804xxx will be textiles for adults whereas 803xx is for babywear. I hope this makes sense
 

Minty

AWF VIP
Local time
Today, 12:32
Joined
Jul 26, 2013
Messages
10,354
It does, but I wouldn't religiously stick to 5 digits.
What happens if you exceed 99 textiles for adults or babywear?

Why not have two fields - a category code and an stock identifier. The part code could then be a the 2 parts joined for display purposes. 804-12345 etc.
 

isladogs

MVP / VIP
Local time
Today, 12:32
Joined
Jan 14, 2017
Messages
18,186
I cannot change to cascade update because it states there is a error, about having records.

That is easily fixable.
Run the unmatched query wizard to identify records missing in one or other tables.
Deal with the discrepancies by adding (or deleting) records as appropriate.
When done, apply referential integrity plus cascade update (and if appropriate cascade delete)
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Jan 23, 2006
Messages
15,361
Workflow,

Others have given good advice and suggestions. I'm attaching a document that also outlines database design principles that you my find useful.

Good luck.
 

Attachments

  • rdbmsprinciples(1).pdf
    78.4 KB · Views: 101

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 19, 2002
Messages
42,970
Just to reiterate, its not a Primary Key

Then what is the primary key?

If you don't have a primary key, then you have never enforced Referential Integrity and that would explain why you cannot get cascade update to work. Colin suggested how to find the orphan records. Once you find them though, you have to figure out what to do with them since they are not related to any valid product.

I cannot and do not wish to use autonumbers
It probably seems overwhelming to make this change. If you ever create another app, you can start it off on a better foot.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:32
Joined
Sep 12, 2006
Messages
15,613
A surrogate key is an autonumber key which serves to identify the row. The linked tables then use the same number value to link the tables, rather than the product code etc.

That way the product code and description is ONLY in the product table, and you can change it to whatever you want.
 

Users who are viewing this thread

Top Bottom