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

[gema57]

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]

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]

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.

 

[Galaxiom]

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.

 

[Cronk]

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

   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]

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:

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:

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

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]

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]

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]

How to upload images please ?

 

[MajP]

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

 

[gema57]

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]

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

 

[MajP]

I believe you can zip a file and upload before ten

 

[gema57]

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]

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

 

[gema57]

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.

 

[MajP]

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]

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 ?

 

[MajP]

Was not suggesting the Access issue was related to the OS. Was suggesting the ability to post a .zip file to the site could be related to the browser or OS. My work computer cannot post an attachment due to security settings.

 

[gema57]

Was not suggesting the Access issue was related to the OS. Was suggesting the ability to post a .zip file to the site could be related to the browser or OS. My work computer cannot post an attachment due to security settings.

I need to post just another post, and I will be authorized to send an image. I will take a look where can I post, and I after I will be free