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

gema57

Registered User.
Joined
Jan 24, 2019
Messages
17
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.
 
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.

attachment.php


Data
attachment.php


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

Attachments

  • reflexiveRelationship.jpg
    reflexiveRelationship.jpg
    9.9 KB · Views: 504
  • ReflexiveData.jpg
    ReflexiveData.jpg
    11.3 KB · Views: 501
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.
 
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.
 
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.
 
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
 
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! :eek: https://www.access-programmers.co.uk/forums/showthread.php?t=303233
 
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.
 
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.
 
Last edited:
Go to the "Go Advanced" and you can upload files.
 
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 :D -, then I cannot see "Go Advanced", just the image, and the system asked me an adress, that I don't have.
 
OK, OK Iam sorry, :banghead: I found it.
The system refused to allow to post images, because I have less than 10 posts.
 
I believe you can zip a file and upload before ten
 
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.


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.
 
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.
 
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 ?
 

Users who are viewing this thread

Top Bottom