?
Solved

Prevent Foreign Key to become orphan

Posted on 2014-12-23
13
Medium Priority
?
113 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 2000 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
Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

 

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
 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

765 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