8,192 views

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
@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.

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.

@All,

"Since it is possible to set any department "NULL" in the STUDENT table by imposing "ON-DELETE, SET NULL" constraint on the foreign key during the schema definition."

So in Student table if I am making Dept_id as null, then whether it should be updated in Department table in Dept_id. And in department table if it gets updated, then we are allowing "null" to be there dept_id in department table. But since this is a primary key in department table, then it should not allow.

So how this operation gets successful?

https://www.mssqltips.com/sqlservertip/2365/sql-server-foreign-key-update-and-delete-rules/

edited

@Arjun sir

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

In that case Query 2 will also work fine ??

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.

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

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.

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.

by