April 22, 2008 at 2:30 am
Hi
I've got a model in which two tables, A and B refer to each other. So I can't insert into A without inserting into B first, and vice versa, due to the foreign key constraints. In Oracle I would solve this with a deferrable foreign key, allowing me to insert the two necessary rows in one operation, and then have the constraint checked at commit time. This does not seem possible in SQL Server? Or am I overlooking something?
April 22, 2008 at 3:05 am
[font="Verdana"]Can you tell us, why you need to check the values in first(Parent) table? What exactly you are trying to do?
While records get inserted into second (Child) table, it indirectly get ensured that Parent table has related records with Child table. Let us know, what cause you to search such thing.
Mahesh[/font]
MH-09-AM-8694
April 22, 2008 at 3:46 am
I have a "Group" and a "GroupMember" table, and the rule is that a group cannot exist without a master, and a master is a member:
Group(Id, Name, MasterId)
GroupMember(GroupId, MemberId)
So Group.Master references GroupMember.MemberId, and GroupMember.GroupId references Group.Id.
Also note that GroupMember.MemberId actually points to an third table where the actual object lives.
The current solution is to have the Group.MasterId reference the same field as the GroupMember.MemberId, and to use a check in the service layer, but I prefer having my constraints declared where they belong, i.e. close to the data.
April 22, 2008 at 4:00 am
I don't know whether it's possible to do it the way you describe. But one alternative would be to remove the MasterId column from the Group table and add an IsMaster column to the GroupMember table. You would have to use triggers to make sure that each group has exactly one IsMaster member.
John
April 22, 2008 at 4:42 am
Putting a IsMaster flag in the cluster member was one of the ideas I rejected initially because it would require a procedural check which I would like to avoid. It also just feels more right to keep this in the Group table as the master is a property of the group...
So unless SQL Server can do deferrable constraints I'll just stick with my current (suboptimal) solution.
April 22, 2008 at 6:01 am
No, MS SQL does not have deferred constraints like Oracle does.
April 22, 2008 at 6:05 am
Thanks for the answer. It's a shame though :/
April 22, 2008 at 6:09 am
I don't know about being a shame. That is one of the features in Oracle that is both a blessing and a curse. In most cases, I think it is better to force the developers to insert and update data in the correct order.
Having spent a lot of time in both Oracle and MS SQL, this is one of the "features" of Oracle that I have seen mis-used a lot.
April 22, 2008 at 6:16 am
But in this particular case there is no correct order.
Also, I'm pretty sure that every language feature ever created has also been abused by a sufficiently uninformed developer 🙂
April 22, 2008 at 6:30 am
True, but as a database professional, I get nervous about features that tend to allow for a poor database design. This is one of them.
Not to say MS SQL Server does not have a few as well.
April 23, 2008 at 5:25 am
Would splitting the 2 tables into 3 work?
membertable (membid, groupid)
grouptable(groupid, name, mstrid)
mastertable(mstrid,groupid,membid)
Or have I completely missed the point?
April 24, 2008 at 7:06 am
andre.naess (4/22/2008)
But in this particular case there is no correct order.
I feel your pain as I've also come across a very similar situation (i.e., the order of events couldn't be controlled) where I could have used a deferrable constraint which is lacking in SQL Server.
As for misuse (in Oracle) I didn't find it to be a problem as I only enabled that property (default is NOT) after very careful review of the application and the business logic that forced the situation.
I believe that I used triggers to get the job done.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply