edited by
10,851 views
38 votes
38 votes

Consider two tables in a relational database with columns and rows as follows:

$$\overset{\text{Table: Student}}{\begin{array}{|c|c|c|} \hline \textbf {Roll_no} & \textbf{Name} & \textbf{Dept_id} \\\hline  1& \text{ABC} & 1\\\hline 2& \text{DEF} & 1 \\\hline 3 & \text{GHI} & 2\\\hline 4 & \text{JKL} & 3\\\hline  \end{array}} \qquad \overset{\text{Table: Department}}{\begin{array}{|c|c|c|} \hline \textbf {Dept_id} & \textbf{Dept_name} \\\hline  1& \text{A} \\\hline 2& \text{B}  \\\hline 3 & \text{C} \\\hline  \end{array}}$$

Roll_no is the primary key of the Student table, Dept_id is the primary key of the Department table and Student.Dept_id is a foreign key from Department.Dept_id
What will happen if we try to execute the following two SQL statements?

  1. update Student set Dept_id = Null where Roll_on = 1
  2. update Department set Dept_id = Null where Dept_id = 1
  1. Both i and ii will fail
  2. i will fail but ii will succeed
  3. i will succeed but ii will fail
  4. Both i and ii will succeed
edited by

3 Answers

Best answer
48 votes
48 votes
Answer is C

Here in (i) when we update in STUDENT table Dept_id = NULL it is fine as a foreign key can be NULL.

But in (ii) if we set in DEPARTMENT  table dept id = NULL it is not possible as PRIMARY KEY cannot be NULL.

Instead of update to NULL, if we try DELETE, then also it is not allowed as we have foreign key reference to it from STUDENT table with Dept_id = 1. DELETE ON CASCADE clause is a way to avoid this issue which will delete all referenced entries from the child table too but unless told we cannot assume this as this cause is not universally applicable.
edited by
11 votes
11 votes
"NO PRIME ATTRIBUTE CAN HAVE NULL VALUE" falls under entity integrity. Hence (ii) will fail.And (i) will succeed.

So answer c.
3 votes
3 votes

Let me give you a real life example. Suppose you have a database that stores sales proposals. Suppose further that each proposal only has one sales person assigned and one client. So your proposal table would have two foreign keys, one with the client ID and one with the sales rep ID. However, at the time the record is created, a sales rep is not always assigned (because no one is free to work on it yet), so the client ID is filled in but the sales rep ID might be null. In other words, usually you need the ability to have a null FK when you may not know its value at the time the data is entered, but you do know other values in the table that need to be entered. To allow nulls in an FK generally all you have to do is allow nulls on the field that has the FK. The null value is separate from the idea of it being an FK.

have a look - https://stackoverflow.com/questions/7573590/can-a-foreign-key-be-null-and-or-duplicate

Answer:

Related questions

43 votes
43 votes
5 answers
1
Ishrat Jahan asked Nov 2, 2014
10,532 views
A table T1 in a relational database has the following rows and columns: $$\begin{array}{|c|c|c|} \hline \text {Roll no. } & \text {Marks} \\\hline 1& 10 \\\hline 2 & 20 ...
43 votes
43 votes
6 answers
4
Ishrat Jahan asked Nov 2, 2014
12,131 views
Consider the following schedule $S$ of transactions $T1$ and $T2:$$${\begin{array}{l|l}\textbf{T1}& \textbf{T2} \\\hline\text{Read(A)} \\\text{A = A – 10}\\& \text...