Prevent Foreign Key to become orphan

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.
JimiJ13I T ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JimiJ13I T ConsultantAuthor Commented:
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
Olaf DoschkeSoftware DeveloperCommented:
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

JimiJ13I T ConsultantAuthor Commented:
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
JimiJ13I T ConsultantAuthor Commented:
Nice & easy.
0
Olaf DoschkeSoftware DeveloperCommented:
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
JimiJ13I T ConsultantAuthor Commented:
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
Olaf DoschkeSoftware DeveloperCommented:
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
JimiJ13I T ConsultantAuthor Commented:
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
Olaf DoschkeSoftware DeveloperCommented:
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
JimiJ13I T ConsultantAuthor Commented:
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
Olaf DoschkeSoftware DeveloperCommented:
>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
JimiJ13I T ConsultantAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.