Foreign Keys and NULL
Let us take the relation:
|
DEPT
|
|
DEPT ID
|
DNAME
|
BUDGET
|
|
D1
|
Marketing
|
10M
|
|
D2
|
Development
|
12M
|
|
D3
|
Research
|
5M
|
|
EMP
|
|
EMP
ID
|
ENAME
|
DEPT
ID
|
SALARY
|
|
E1
|
Rahul
|
D1
|
40K
|
|
E2
|
Aparna
|
D1
|
42K
|
|
E3
|
Ankit
|
D2
|
30K
|
|
E4
|
Sangeeta
|
|
35K
|
Assume that Sangeeta is not assigned any section. In the EMP tuple corresponding to Sangeeta, thus, there is no genuine section number that can serve as the appropriate value for the DEPTID foreign key. Thus, one cannot verify BUDGET and DNAME for Sangeeta's department as those values are NULL. This may be actual circumstances where the person has newly joined and is undergoing training and will be allocated to a department only on finishing of the training. Thus, NULL in foreign key values might not be a logical error.
Thus, the foreign key definition may be redefined to conatin NULL as an acceptable value in the foreign key for which there is no require to find a matching tuple.
Is there any other constraint that may be applicable on the attribute values of the entities?
Yes, these constraints are mostly linked to the domain and termed as the domain constraints.