Wednesday, December 13, 2006
DBA Interview Question #7: How would you implement an optional foreign key?
Here's question #7 in our series on DBA Job Interview Questions:
1) Step 1: Create a new column in the child table and make it non-null
2) Step 2: Create a foreign key constraint from this new column to the parent's primary key
After you do these two steps, you will have a mandatory foreign key; any inserts/updates to the child column will require a matching parent. Is it possible to insert a value at the child that has no matching parent? No. What if you tried to insert a NULL value into the child column - would it succeed or fail? It would fail - this is a non-nullable column.
Now, on to the optional foreign key - the words "optional foreign key" are a bit ambiguous, aren't they? Does that mean that the child records will not always be verified against the parent? Does "optional" mean that sometimes the child values are checked and sometimes they aren't? It's confusing, right?
Basically an optional foreign key means this: if a known value is inserted into the child column(s), then it must have a matching parent value. If an unknown value is inserted into the table, then it is allowed. That's all there is to it - nothing more. So, to answer the question succintly and move on to the next question, I would just say, "All you have to do is to make the child column(s) nullable."
Hope this helps!
Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server
How would you implement an optional foreign key?This, of course, depends on your understanding of how primary key/foreign key relationships work and, if you have any gaps in your knowledge, this type of question will expose them to your interviewer. The answer to this question is actually ultra-simple but it requires a little introduction. Let's first review how to implement mandatory foreign keys:
1) Step 1: Create a new column in the child table and make it non-null
2) Step 2: Create a foreign key constraint from this new column to the parent's primary key
After you do these two steps, you will have a mandatory foreign key; any inserts/updates to the child column will require a matching parent. Is it possible to insert a value at the child that has no matching parent? No. What if you tried to insert a NULL value into the child column - would it succeed or fail? It would fail - this is a non-nullable column.
Now, on to the optional foreign key - the words "optional foreign key" are a bit ambiguous, aren't they? Does that mean that the child records will not always be verified against the parent? Does "optional" mean that sometimes the child values are checked and sometimes they aren't? It's confusing, right?
Basically an optional foreign key means this: if a known value is inserted into the child column(s), then it must have a matching parent value. If an unknown value is inserted into the table, then it is allowed. That's all there is to it - nothing more. So, to answer the question succintly and move on to the next question, I would just say, "All you have to do is to make the child column(s) nullable."
Hope this helps!
Technorati Tags: Scott Whigham, Whigham, SQL Server, SQL Server 2005, SQL Server, Microsoft SQL Server
