Reflexive table and the usual message : You can not add or change record because... (1 Viewer)

gema57 · Jan 24, 2019

Hello.
I have to model a stable, and so stallions, which belong to an owner at a date t etc ... so far so good. Except that the person responsible imposes that only Stallions - not mare, why? I know it's weird, but do not ask me why, this is so - have to be modeled, and so have only one father, and this father is in the stable, he does not want to buy others stallions, and does not do not want to register the mares. He wants to have statistics on the stallions, to see their performances at the level of the races, their health, their diseases etc especially to trace them in relation to their father only. So level modeling is little problem, since a horse can only have one and only one father, and a stallion can be the father of 0 to several stallions -only the males will be retained. So far we agree, there is nothing exceptional. Even a first year student is able to modeling it. But it is in the implementation of Microsoft Access that begins the problem. I can not enter a first record, since logically Microsoft Access asks me the father of the first one that does not exist. Questions: How to do it? What to do ? And where to start? I do not have the right to change the relation between tables of the stallion, which must imperatively remain: one-to-many. In fact it is the good old modeling of a reflexive table and its constraints of integrity and foreign keys.

Error Message gives : You can not add or change record because record is required in table. I think it is classical error.
Thx for any help.

MajP · Jan 24, 2019

You are going to have to show us the table relationships. Post an image. You have imposed referential integrity and it is being done improperly.
If you want to know all about animal breeding programs, you come to the right place.
https://www.access-programmers.co.uk/forums/showthread.php?t=302630

MajP · Jan 25, 2019

So I learnt that the correct term is reflexive join, never knew that. Thought it was called a self join. Anyways, I tested this and was able to establish referential integrity without any problem. You likely have required the foreign key (parent id) as a required field. You can leave it blank or select a valid parent.

Data

Even the subdatasheet works, but you have to edit the relation.

Pat Hartman · Jan 25, 2019

You might want to point out to the client that making separate rules for dams and sires doubles the requirements you have to model and that will greatly increase the cost. You might even need to make separate tables and forms for mares and stallions to actually implement two sets of rules.

In a self referencing relationship, the top level has no parent. It makes sense when you think about the structure as an organization chart but when looking at parentage, it does not seem logical that there might not be a parent. On the other hand, how far back in time are you going to try to go?

There is no "except the top level" rule in Referential Integrity. Therefore, the parent must be allowed to be null. You will need to add logic to the BeforeUpdate event of the form that adds horses that makes the stallion required.

Galaxiom · Jan 25, 2019

Pat Hartman said:
There is no "except the top level" rule in Referential Integrity. Therefore, the parent must be allowed to be null. You will need to add logic to the BeforeUpdate event of the form that adds horses that makes the stallion required.

Always best to have the rules at table level wherever possible/practical.

Would there be any problem having a record ID 0 that would be used where the parent is not known? It would be the default parent too.

Record 0 would reference itself as its own parent.

Pat Hartman · Jan 27, 2019

Always best to have the rules at table level wherever possible/practical.

If you make the parent field required, it CANNOT be null. Never under any conditions would a non-null value be valid if a related record does not exist. Therefore record #0 must ALREADY exist in order to be acceptable as a parent.

This is unreconcilable if you have RI enforced AND set the parent field as required. You cannot add the dummy record if RI is enforced AND the parent field is required.

You can remove all RI and the requirement that the parent field be not null. You can then add a dummy record. Once the dummy record is added, you can modify the parent field and fill in the dummy record ID. Then and only then can you reestablish RI and enforce the required rule for the parent field.

I wouldn't use 0 as the default value since 0 is not generated by the autonumber. I would use 1 if I were going to do this.

Cronk · Jan 27, 2019

A zero value can be inserted into an autonumber field - something I'd be inclined to do in this case.

I thought it was possible in access DDL to set initial value and increment of an autonumber but after a quick search I didn't find anything - maybe SQL Server.

However

Code:

   db.execute "Drop table tblYourTable"
   db.Execute "create Table tblYourTable ( ID counter, Field1 Long)"
   db.Execute "Create index PrimaryKey ON tblYourTable (ID) with primary"
      
   Set rst = db.openRecordset("Select * from tblYourTable")
   rst.addNew
   rst!ID = 0
   rst.update

isladogs · Jan 27, 2019

Cronk said:
A zero value can be inserted into an autonumber field - something I'd be inclined to do in this case.

I thought it was possible in access DDL to set initial value and increment of an autonumber but after a quick search I didn't find anything - maybe SQL Server.

Yes its easy to do using COUNTER(start number, increment):

To add a 'standard' autonumber primary key ID field to an existing table starting at 1 with step 1:

Code:

ALTER TABLE TableName ADD COLUMN ID COUNTER(1,1) NOT NULL PRIMARY KEY;

To alter an existing autonumber ID field to start at 5 with step 2:

Code:

ALTER TABLE TableName ALTER COLUMN ID COUNTER(5,2) NOT NULL;

As you say, you can set a zero value (or a negative value) for autonumber if you really want to do so and even make the values DECREMENT if you really must

Code:

ALTER TABLE TableName ALTER COLUMN ID COUNTER(0,-10) NOT NULL ;

Just be careful that you don't incorrectly reset the autonumber whilst setting a PK field in a table with data or you will get into the same mess that I did in this recent thread!

https://www.access-programmers.co.uk/forums/showthread.php?t=303233

gema57 · Jan 28, 2019

Thx to all for your answers. I could not answer immediatlely because I had a -familly's- problem, then I was not able to participate. I will post as soon as it is possible.
Again thx for your help.
Regards.

MajP · Jan 28, 2019

Hope everything works out. There were some side track discussions that IMO are not necessary since there is no need to define dummy records with a 0 or 1 key.

IMO there is No reason to add a dummy parent at all. Does not really give you anything. Lets say you add dummy record with an ID of 1. In order to enforce referential integrity and also require a parent ID, the dummy record Parent ID FK has to relate to something. So normally you relate the top level to themselves and this record gets a Parent FK of 1.
So in my opinion you have created an artificial top layer. Instead if Horse 7 has an unknown parent just make its Parent ID FK 7.
The other option is simply make it not required. If it is blank it symbolizes a top level. The only issue here is that it is a passive versus active choice. More likely to make a mistake unless you prompt the user at input to acknowledge that there is no parent.

Most likely anything you are going to do (assuming this is Access not SQL Server back end) is in recursive code. If you have not done this before see the link I posted which has several recursive calls to populate pedigrees and offspring and do relationship calculations. Either way you kick out of your recursion if the Parent ID FK is null or if the Parent ID FK Equals the PK.

So either enforce RI and make the parent required. Then add the first record and relate it to itself. You can choose or not choose to make a dummy record
Or
Enforce RI but do not maket the Parent FK required. Then add some prompting code to ensure the user intends it to be a top level.

gema57 · Jan 29, 2019

How to upload images please ?

MajP · Jan 29, 2019

Go to the "Go Advanced" and you can upload files.

gema57 · Jan 29, 2019

Go to the "Go Advanced" and you can upload files.

Just to say you, Iam on Chromium-without plugins-, inside Kubuntu 14.04 64 bits, -no body is perfect

-, then I cannot see "Go Advanced", just the image, and the system asked me an adress, that I don't have.

gema57 · Jan 29, 2019

OK, OK Iam sorry, :banghead: I found it.
The system refused to allow to post images, because I have less than 10 posts.

MajP · Jan 29, 2019

I believe you can zip a file and upload before ten

gema57 · Jan 29, 2019

MajP said:
I believe you can zip a file and upload before ten

It does not work however, I think it is more easy if someone among you can retrive my files and post them.

MajP · Jan 29, 2019

Surprised it did not work. According to the site it should
https://www.access-programmers.co.uk/forums/showthread.php?t=223479

gema57 · Jan 29, 2019

MajP said:
Surprised it did not work. According to the site it should
....

Before reaching my critical 10 posts -Iam at 7, just to wait only 3 others posts-, I will answer to your previous post.

MajP said:
So I learnt that the correct term is reflexive join, never knew that. Thought it was called a self join. Anyways, I tested this and was able to establish referential integrity without any problem. You likely have required the foreign key (parent id) as a required field. You can leave it blank or select a valid parent.

Data
....

Even the subdatasheet works, but you have to edit the relation.

Well this is what I've done, I've followed what U wrote, but it does not work.

MajP · Jan 29, 2019

Before reaching my critical 10 posts -Iam at 7, just to wait only 3 others posts-, I will answer to your previous post

I wonder if the issue with posting is related to your OS?

To test. Delete the relationship and test. If it does not work, you have some required field still.

gema57 · Jan 29, 2019

MajP said:
I wonder if the issue with posting is related to your OS?

To test. Delete the relationship and test. If it does not work, you have some required field still.

Let's to be clear, I posted the all previous posts in Linux, however, the work, the database with Microsoft Access was done on Windows 7, and not elsewhere. I don't think U can install and use Microsoft Access inside Kubuntu. In Suse maybe-it belongs to Microsoft- but in the others distributions No. Am I wrong ?

Reflexive table and the usual message : You can not add or change record because... (1 Viewer)

Registered User.

You've got your good things, and you've got mine.

You've got your good things, and you've got mine.

Attachments

Super Moderator

Super Moderator

Super Moderator

Registered User.

MVP / VIP

Registered User.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Registered User.

Registered User.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Registered User.

You've got your good things, and you've got mine.

Registered User.

Similar threads

Users who are viewing this thread