Solved

Prevent Foreign Key to become orphan

Posted on 2014-12-23
13
110 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
In this article I will describe the Copy Database Wizard 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.

732 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