Solved

Prevent Foreign Key to become orphan

Posted on 2014-12-23
13
99 Views
Last Modified: 2015-01-01
Hello Experts,

How to effectively prevent Primary Keys (Comm.ComID & Cons.ConID) from being deleted or changed when
they are already being used in Order.RefNo?    
 Diagram that shows relationship between table with caption
Thanks.
0
Comment
Question by:JimiJ13
  • 7
  • 6
13 Comments
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 40515730
You would design two foreign key columns and a constraint exactly one of these is set. Aside of that additional constraint you have now normal foreign keys with normal FK constraints.

ALTER TABLE MyTable
ADD CONSTRAINT ExactlyOneRefnoIsNotNull
CHECK (
(CASE WHEN commrefno IS NOT NULL THEN 1 ELSE 0 END
    + CASE WHEN consrefno IS NOT NULL THEN 1 ELSE 0 END)
    = 1
)

Open in new window


On top of that you can add a computed field refno with computedcolumnspecification formula COALESCE(commrefno,consrefno)

Bye, Olaf.
0
 

Author Comment

by:JimiJ13
ID: 40515988
Olaf,

I tried and I think this is really the best way to go for now.
BTW, I'm using MS SQL 2012


Great!
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40516040
Just one thing that crossed my mind: Of course these two foreign keys now need to have a less strict foreign key constraint than normal, which allows both a valid foreign key existing in the parent table, but also NULL. But off the top of my head this is not exotic even for normal foreign keys (when they are optional relations to other data). Just a detail.

Bye, Olaf.
0
 

Author Comment

by:JimiJ13
ID: 40516086
I understand. In this abnormal case, it is necessary to go beyond normal solution as well. Anyway, I can do extra validations on the programming side to compensate the missing portion.  Your solution is far better than what I originally thought.

Thanks.
0
 

Author Closing Comment

by:JimiJ13
ID: 40516089
Nice & easy.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40516137
Fine, I also thought about separating the two new fields and the computed field into a new table, so the order will just reference this new table and it encapsulates the branch to comm or cons. The only benefit would be, you can replace it by something else later, if you get a better idea and your oder table only has its RefNo field as before.

This kind of relation It's not part of normal ERMs, but SERM (structured entity relationship model) knows something called a generalisation/specialisation relation, which this resembles, as far as I see.

The provided solution is easily extended, if you need a third relation, so I'd say this already is one of the best solutions to it.

To get a more normal design you would need to put comm and cons data into one table and give it a bit column deciding the type of data or add another head table with just the common data of the two, with one of comm or cons table exclusively extending this head data. Then you have a normal relation between orders and this new comm/cons head data.

Bye, Olaf.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:JimiJ13
ID: 40518053
Thanks Olaf for that great insights!

Your first suggestion is quite complicated, not so much for me, for the one who will maintain the application.  

Your last suggestion is not very appealing as well because even the "description" which common but define different
meaning.  

The solution I currently considered best is non-complicated and very easy to grasp and maintain by other developers.

Cheers.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40518212
I don't know of which third solution you speak of.
I suggested either a little complexer than normal constraint for the foreign keys. I don't think counting the NULLs is hard to grasp.

Then I suggested a redesign. It doesn't necessarily put the common fields of cons and comm into the head table. I don't know what real world concepts comm and cons really model, but both of them seem to be orderable or orders might be comm or cons.

Bye, Olaf.
0
 

Author Comment

by:JimiJ13
ID: 40520256
Olaf,

I considered your 1st comment as the best solution and my original design with check constraint and triggers is the still the closest contender. We need to decide, which is which with my client before we start the program development.

BTW, suggestions in this thread will be considered in the final decision: http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28586190.html

Thanks.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40523782
I see. I'd always opt for a check constraint rather than triggers. First of all you need to maintain both update and insert triggers for checking the validity, then you interrupt an insert/update and have to put it at the end yourself. A check constraint simply is checked on top of the insert/update.

The triggers have the only advantage, you could change the values coming in by code to conform to the rules and get the insert or update to work correctly instead of failing by the constraint. but in what circumstances does this really make sense? A foreign key will not be typed in by the user, so the only errors you'd correct would be your own column addressing errors, for example. That should show up in testing and should be handled by correct code rather than triggers.

You can of course also write the triggers to do the same as the check constraint: Refuse to insert/update, if the rules are broken. But that's what check constraints are made for. (instead of) insert/update triggers should either compute things on the fly (we also have computed fields for that), do audit trails (logging of changes) - we have transaction log or cdc for that - or merge data (we have MERGE for that) or doing anything else with the data instead of the insert or update. Using it merely, because insert and updates trigger it and your code hooks in at that event moment, is misusing it, if you ask for my opinion.

Bye, Olaf.
0
 

Author Comment

by:JimiJ13
ID: 40525092
Thanks Olaf! I will study your opinion.

BTW,  the Insert validation will be taken cared by the Web App combo box - by limiting the selection based on the contents of the source.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 40525253
>the Insert validation will be taken cared by the Web App combo box

That's good. But it's still just an exception of the rule to not do things multiple times. Database restrictions for data integrity always are good on top of any business or application logic, as they prevent errors from any source.

Preventing errors redundantly already on the UI level saves you to catching error feedback from the database, but doing something on the application level is no reason to skip the check constraint on the database level, because it's only a current truth the only source of that data is the web app.

Bye, Olaf.
0
 

Author Comment

by:JimiJ13
ID: 40526596
In other words, there are a lot of things needed to be done in order to protect data integrity based on my original DB structure. That's what I'm seeing as well though it looks nice to have a single fK.  

Thanks Again.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
Sql Stored Procedure 26 28
Word Template Mail merge with vb.net 4 40
Mssql SQL query 14 28
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now