GATE IT 2004 | Question: 78

Question

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

• A. Both i and ii will fail
• B. i will fail but ii will succeed
• C. i will succeed but ii will fail
• D. Both i and ii will succeed

Comments

@manojk sir just check it     in (i) inplace of  dept id =NULL if it is dept id =5 then answer will be A. here this is possible bcz of null that  never be  refer to  any table .

---

1. update Student set Dept_id = Null where Roll_no = 1. Please, correct typing mistake in question. If it is not a mistake then query will fail as no such column in table and answer A.

---

A foreign key value must either be NULL or match a primary key value in the referenced table

Answer 1

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.

Comments

@Arjun sir

Here why we don't consider here ON DELETE SET NULL / CASCADE ?

In that case Query 2 will also work fine ??

---

@Abhijit Sen 4@jatin khachane 1 ON UPDATE/DELETE SET NULL/DEFAULT/CASCADE is used when we create a table so that referential integrity will not be violated while doing any change in parent table.Since, It is not mentioned in the question that we have to use referential integrity constraints, So, We should not have to consider it here for query 2.

If We use it then also $2^{nd}$ query will be failed because primary key can't be null. So, it will show error when we execute it.

Suppose, If Dept_id of Department table is not a Primary Key and we have used On Update Set Null constraint between parent table 'Department' and child table 'Student' to maintain referential integrity. Now if we initially execute  query $2^{nd}$ then it contains $(Null,2,3)$ in Dept_id column of Department table and due to referential integrity constraint , Dept_id column of student table will contain (Null,Null,2,3). So, In this case Query 2 will be executed successfully.

---

Student.Dept_ID is a foreign key from Department.Dept_Id means Student is a referencing table and Department is a referenced table.

Answer 2

"NO PRIME ATTRIBUTE CAN HAVE NULL VALUE" falls under entity integrity. Hence (ii) will fail.And (i) will succeed.

So answer c.

Comments

kshitij86

are you sure?

suppose there is a relation R(a,b,c) in which both a and b combinely form the primary key then a and b both are prime attributes here so can i have a tuple like  (1,null,c)  in this  relation?

@Arjun sir

---

@Pranavpurkar No, NULL values are not allowed in Primary Key. 

 

When you create a primary key constraint, none of the columns included in the primary key can have NULL constraints; that is, they must not permit NULL values. 

-Oracle https://docs.oracle.com/javadb/10.10.1.2/ref/rrefsqlj13590.html

Another Source: database - What's wrong with nullable columns in composite primary keys? - Stack Overflow

 

---

Abhrajyoti00

yup:)

Answer 3

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 4

BEST ANSWER