0

First of all, my apologies if this question is a duplicate - but I find it difficult, putting short, precise words on my problem.

current structure

I've got these entities. The left contains several groups (like in Unix, in order to make data available to a whole group at once) and at the moment, it's always 1. The right one contains projects - and the middle one makes sure, that one group can gain access to several projects.

As you can see, there are foreign key relationships among them. Now, I want to create a new project in nmd__tree. When doing that, it returns an error:

Cannot add or update a child row: a foreign key constraint fails (nmd.nmd__tree, CONSTRAINT FK_nmd__tree FOREIGN KEY (treeid) REFERENCES nmd__helperusergrouphierarchy (treeidfk))

This makes sense, since the nmd_tree relies on a valid foreign key in the helper entity - but at the same time, it presents the problem, that the treeidfk isn't yet known, since it is autogenerated in nmd__tree

A solution could be to remove the relations, insert the record in nmd__tree, extract the newly written primary key (treeid) and create a record in the middle helper entity with the new id. It will work, but is really not very elegant. Also, removed relations presents other, potential problems.

My intentions are to create a query, that deals with this problem by creating both records at once. I know, it isn't possible to make a double insert and found this suggestion (my version doesn't write any records), as well as an article, suggesting stored procedures, which I don't see should make a difference

I would really appreciate a push in the right direction, please.

Community
  • 1
  • 1
Anders
  • 251
  • 3
  • 13

1 Answers1

1

It seems you've got your constraints defined in the wrong direction; The middle table should have two foreign key constraints not the two end tables. That way, you can insert records in the two end tables and then link them up using the middle table.

rogierschouten
  • 153
  • 6