one to one relation problem in SQL Server BE. Related int. field is not taking the value (1 Viewer)

SORM2

New member
Local time
Today, 13:49
Joined
Jan 16, 2021
Messages
25
Hello Everyone, I have an relation issue which is working in MS Access but not in SQL Server BE.
That was my relation completely same when I had BE also in MS Access. Now I did this in SQL Server BE with linked table.

I have following table structure and database relation between two tables.

==>I have two tables.
First is Vehicle, Second is Plan table

* Vehicle table fields (vehicleid (PK), vehicle, model, brand)
* Plan table fields (planid (PK), vehicleid, plandate1, plandate2, notes)

Relation is 1 to 1. (One vehicle can have one planning record. and one plan can be belong to one vehicle)
Therefore, relation is from vehicleid (PK) from vehicle table to vehicleid of plan table. (vehicleid of plan table is unique key)

In the form, when I I create one new vehicle and then entered plandate1, the problem is, vehicleid field of plan table, which is bound to auto incremental PK field of Vehicle table which is vehicleid, is not taking the same value.

In other words, vehicle id (PK) value is auto incremental so it is taking its value, but the problem, vehicleid of plan table which is unique key is not taking the same value as vehicleid (PK).

It was taking automatically the same number as vehicleid PK when it was in MS Access. What did it change? Where am I doing wrong?

I also attached the MS Access file while BE and FE is together in MS Access. You can look at it and see how plan.vehicleid field is taking the same value when vehicle.vehicleid (PK) takes autonumber.

Many thanks in advance
 

Attachments

  • onetoonerelation.accdb
    504 KB · Views: 398

Minty

AWF VIP
Local time
Today, 10:49
Joined
Jul 26, 2013
Messages
10,355
You very rarely need a 1 to 1 relationship.
If those fields in your plan table can only belong to one vehicle then they should simply be in the Vehicle table.

What information is stored in the Plan table?
 

SORM2

New member
Local time
Today, 13:49
Joined
Jan 16, 2021
Messages
25
You very rarely need a 1 to 1 relationship.
If those fields in your plan table can only belong to one vehicle then they should simply be in the Vehicle table.

What information is stored in the Plan table?
Hello Minty, Plan table has a lot of date fields and note fields. Vehicle table has a lot of other relations with other tables. So, I do not think it is a good idea to take all parameters to Vehicle and detroy plan table. And secondly I would like to know why it is not working in SQL Server BE. It is so nonsense.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:49
Joined
May 7, 2009
Messages
19,175
it is but Normal!
you need to enter something in tblPlan for the vehicleid to be carried.

see form1 and add some data to plan-subform.
do not fill the vehileid and planid, only the other fields.
 

Attachments

  • onetoonerelation.accdb
    800 KB · Views: 210

Minty

AWF VIP
Local time
Today, 10:49
Joined
Jul 26, 2013
Messages
10,355
You would have to set up a relationship in SQL server.
But if your form/subform master child properties are set up correctly that wouldn't be necessary.
 

SORM2

New member
Local time
Today, 13:49
Joined
Jan 16, 2021
Messages
25
You would have to set up a relationship in SQL server.
But if your form/subform master child properties are set up correctly that wouldn't be necessary.
Hello Minty I had relationship in SQL Server between unique integer value planid and vehicleid (PK) which is 1 to 1.
As I explained in my first message, planid is not taking the value of vehicleid after new entry. But it was taking in MS Access BE.
 

SORM2

New member
Local time
Today, 13:49
Joined
Jan 16, 2021
Messages
25
it is but Normal!
you need to enter something in tblPlan for the vehicleid to be carried.

see form1 and add some data to plan-subform.
do not fill the vehileid and planid, only the other fields.
You say it is normal, but did you look at in Access file "vehicleplanform"? you can see there planid is taking the number value automatically same as vehicleid of Vehicle table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:49
Joined
May 7, 2009
Messages
19,175
please google Autonumber field.

even if you create new record on Vehiicle table direct to MSSSQL using Azure Data Studio, no new
record will be created on plan table, unless you have a store-procedure that will do that.
in access, it is called data macro.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:49
Joined
Sep 12, 2006
Messages
15,614
Even if you only want to have one plan record for each vehicle, it's best to design the database as if you could have more. (or fewer). That's the point. It's not just 1 to 1, It may be 1 to 0

Therefore, you need the one side to be the vehicle. Now the plan has it's own ID., but you store the vehicleID in the plan table as a foreign key, and make it unique, indexed. So at the start, you may not have any plan, but when you create a plan, there can only be one for each vehicleid. Alternatively, you could make the PK of the plan table just a Number, rather than an autonumber, and then set it to the same value as the VehicleID. Either way will work.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 19, 2002
Messages
42,981
In a 1-1 relationship (which this isn't btw), the left side (parent) uses an autonumber as the PK but the right-side (child) does not. Like all other relationships, the FK is a long integer. It is impossible to keep two autonumbers in sync and you don't have to.

I'm guessing that you do need a plan table but it won't look like the one you have. Just the few fields you displayed indicate at least one repenting group. Theses dates almost certainly should be stored as rows in the "plan" table.

Access is more friendly than SQL Server and does some things to specifically help people who don't actually understand database design. It is quite possible that in SQL Server, you cannot insert both a parent and a child in a single query, although once you fix the FK data type, it might start working for you. In a 1-1, it is silly to give the child-side PK a name different from the parent-side. Use the same pattern you use for other relationships. Name the FK the same as the PK.
 

SORM2

New member
Local time
Today, 13:49
Joined
Jan 16, 2021
Messages
25
In a 1-1 relationship (which this isn't btw), the left side (parent) uses an autonumber as the PK but the right-side (child) does not. Like all other relationships, the FK is a long integer. It is impossible to keep two autonumbers in sync and you don't have to.

I'm guessing that you do need a plan table but it won't look like the one you have. Just the few fields you displayed indicate at least one repenting group. Theses dates almost certainly should be stored as rows in the "plan" table.

Access is more friendly than SQL Server and does some things to specifically help people who don't actually understand database design. It is quite possible that in SQL Server, you cannot insert both a parent and a child in a single query, although once you fix the FK data type, it might start working for you. In a 1-1, it is silly to give the child-side PK a name different from the parent-side. Use the same pattern you use for other relationships. Name the FK the same as the PK.

Hello Pat Hartman,

Thanks for your answer. But my relation is already the same what you told. Did you look at my Access which I sent in my first comment as attachment? One table's (Vehicle) PK auto-inc. field is linked to other table's (Plan) integer field. And the question is why is to enter new record to both table not provided in SQL server in one query, while it happens smoothly in MS Access. I do not have to enter value first to parent (Vehicle table) in order to create a plan for a vehicle in MS Access. But the same thing is not happening in SQL Server.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 19, 2002
Messages
42,981
I did and that is how I knew you were not using the keys correctly.

A 1-1 relationship is linked PK to PK. That is what the database engine looks at to determine the relationship. If it is linked PK - FK, it is a 1-m relationship as far as the db engine is concerned regardless of the fact that you have defined the FK as being unique.

So, if you actually want to implement it correctly, you need to do it the way i described.

You have enforced a 1-1 by making the FK unique. That works but will confuse people because looking at the relationship will tell them that this is a 1-m and the only way to determine the constraint is by opening the child table in design view and looking at the properties of the FK. You are obfuscating the actual relationship by not defining it correctly.

And I told you why it probably doesn't work in SQL Server, SQL Server can't create both records in one query even though Access can. Fix your form to be a mainform and a subform and bind the main form to the parent table and the subform to the child table. This will NOT insert the child row automatically which is why I'm sure you are doing it the way you are. You can get around this problem by simply using a left join when joining the two tables. That will bring back the parent record even if the child was never inserted.

Open the query in Access and try to add both records using the query. If it fails, you know you cannot do what you are trying to do. Then go to SQL server and recreate the query with the join and try it there. You might get a better error message. If you add the parent record first, you can then add the child record in the join query but you will need to populate the FK. Understand that even though you think this add is being done in a single step, it is NOT. The parent row MUST be inserted first and committed and only then will the FK value be known. The query engine then inserts the child table and commits it. Access is friendly enough to let you think this is being done in one step. SQL Server is not so friendly.

And I agree with the others completely. This is not a 1-1 relationship and should not be defined as such. You have just taken a few fields and declared them as being somehow "separate" for no reason that I can determine.

There is a another very useful query you can run in Access. It both adds new rows and updates existing rows by using a right join. This query will also not work in SQL Server for the same reason. It requires multiple steps.

Now that you have moved to SQL Server, if you want to implement this 1-1, you can do it with a form/subform as I mentioned above OR you can do it by using an unbound form (which I am NOT recommending) and handling all the fetching/saving logic yourself. You would have to enclose the saving part inside a transaction so you can ensure that both or neither rows get inserted. That is how Access handles the situation. The query is run inside a transaction. The first row is committed and the second row is committed and the transcacion ends. If the parent row cannot be committed, the transaction is rolled back or if the child record cannot cannot be committed, the parent row is rolled back and the parent record is deleted.

I can't tell what made you think you had a 1-1 relationship but you might want to find a more technically sound source.
 
Last edited:

SORM2

New member
Local time
Today, 13:49
Joined
Jan 16, 2021
Messages
25
I did and that is how I knew you were not using the keys correctly.

A 1-1 relationship is linked PK to PK. That is what the database engine looks at to determine the relationship. If it is linked PK - FK, it is a 1-m relationship as far as the db engine is concerned regardless of the fact that you have defined the FK as being unique.

So, if you actually want to implement it correctly, you need to do it the way i described.

You have enforced a 1-1 by making the FK unique. That works but will confuse people because looking at the relationship will tell them that this is a 1-m and the only way to determine the constraint is by opening the child table in design view and looking at the properties of the FK. You are obfuscating the actual relationship by not defining it correctly.

And I told you why it probably doesn't work in SQL Server, SQL Server can't create both records in one query even though Access can. Fix your form to be a mainform and a subform and bind the main form to the parent table and the subform to the child table. This will NOT insert the child row automatically which is why I'm sure you are doing it the way you are. You can get around this problem by simply using a left join when joining the two tables. That will bring back the parent record even if the child was never inserted.

Open the query in Access and try to add both records using the query. If it fails, you know you cannot do what you are trying to do. Then go to SQL server and recreate the query with the join and try it there. You might get a better error message. If you add the parent record first, you can then add the child record in the join query but you will need to populate the FK. Understand that even though you think this add is being done in a single step, it is NOT. The parent row MUST be inserted first and committed and only then will the FK value be known. The query engine then inserts the child table and commits it. Access is friendly enough to let you think this is being done in one step. SQL Server is not so friendly.

And I agree with the others completely. This is not a 1-1 relationship and should not be defined as such. You have just taken a few fields and declared them as being somehow "separate" for no reason that I can determine.

There is a another very useful query you can run in Access. It both adds new rows and updates existing rows by using a right join. This query will also not work in SQL Server for the same reason. It requires multiple steps.

Now that you have moved to SQL Server, if you want to implement this 1-1, you can do it with a form/subform as I mentioned above OR you can do it by using an unbound form (which I am NOT recommending) and handling all the fetching/saving logic yourself. You would have to enclose the saving part inside a transaction so you can ensure that both or neither rows get inserted. That is how Access handles the situation. The query is run inside a transaction. The first row is committed and the second row is committed and the transcacion ends. If the parent row cannot be committed, the transaction is rolled back or if the child record cannot cannot be committed, the parent row is rolled back and the parent record is deleted.

I can't tell what made you think you had a 1-1 relationship but you might want to find a more technically sound source.
Dear Pat Hartman, Firstly I thank you for your time such a long explanation. I read it carefully and I understand that Ms Access is doing some user-friendly algorithms and the people who starts with MS Access as me suppose those are the proper database algorithms and expect the same working mechanism from other databases such as SQL Server.

I did another table even though it was 1to1. Because vehicle table has a lot of 1-m relations with other tables and I though it will make my stuff hard, if I do not make seperate table for planning data which includes 10 date fields.

But anyway. I solved the issue differently using VBA. After I saw that vehicle data (as parent) need be saved first, I had two option. either putting a button in current form to open vehicle data form to make a record there first or to save vehicle data in my current unique form. So after specific vehicle fields record will be saved automatically with some coding in the unique form. So when it comes to next cell which is plan data, it finds its parent id and is saved smoothly.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 19, 2002
Messages
42,981
As I said earlier, if you had created a subform for the right-side of the relationship, you would not have needed any code at all. The subform doesn't have to look like a subform. It can be single record with no borders and no scroll bars, etc. As long as the backgrounds are the same, it will be indistinguishable from the main form. If you are going to use Access, you might as well become one with it and understand what it is doing for you. We pay a high price for using a RAD (Rapid Application Development) tool in that we give up a great deal of control and we must distribute the Access runtime if our users do not own a commercial copy of Access. At least take advantage of the RADness features. There is nothing inherently better about writing your own code to replicate an Access feature. In fact, it is detrimental.

I'm sorry you are not willing to take the expert advice and normalize your schema. But at least you understand now why something that worked with Jet/ACE does not work with SQL Server.

Perhaps you don't understand when you join tables, you only select the columns you want from each table. You then use criteria to only select the rows you want.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:49
Joined
Sep 12, 2006
Messages
15,614
Hello Pat Hartman,

Thanks for your answer. But my relation is already the same what you told. Did you look at my Access which I sent in my first comment as attachment? One table's (Vehicle) PK auto-inc. field is linked to other table's (Plan) integer field. And the question is why is to enter new record to both table not provided in SQL server in one query, while it happens smoothly in MS Access. I do not have to enter value first to parent (Vehicle table) in order to create a plan for a vehicle in MS Access. But the same thing is not happening in SQL Server.

How do you update two tables at the same time with a single query in Access? I don't think you can. In a 1 to 1 relationship, you still have to designate one of 2 tables as the master. You then take steps to ensure that the child table can have zero or 1 record.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:49
Joined
Feb 19, 2002
Messages
42,981
If you join any two tables with a relationship either 1-1 or 1-m, you can add records to both the left-side and the right-side by simply entering at least one field in the right-side table. The query MUST include the FK field but Access populates it. You do not. Access automagically fills in the FK for you. Since we all know that the 1-side has to be created first, we can surmise that Access does this within a transaction (I'll explain later) so that it adds the 1-side first, obtains the PK and inserts it as the FK as it inserts the right-side.

The way you know that Access runs action queries inside a transaction is because it always asks you to commit the update.
AppendMsg.JPG


You can update both sides of a join in SQL Server as long as the 1-side record exists. So, you can use a left join because the right-side row doesn't exist. Open the query and type something in the right side. SQL server just doesn't let you add both sides at the same time. But it does let you add a new left and if the left exists, it lets you add a new right.

Most of my forms are based on queries that include joins. That doesn't mean that the forms allow updates to both tables. Normally the joins are to lookup tables and the selected fields are locked. An example of this would be the details section of an order. When you select the product from the combo, you usually want to see other fields in addition to just the product name and the simplest way to do that is to use a left join to the product table. Or in the order header form, you have a join to the customer so you can see other details about the customer in addition to just his name. But it is important to lock the lookup fields because if you don't, Access will let you update them and that would be poor.
 
Last edited:

Users who are viewing this thread

Top Bottom