The following SELECT statement in code
stmt = DB.prepare("select id,name,state,zip " + "from customer " + "where " + "phone = ? " + "and active = ?"); stmt.setString(1, customerPhone); stmt.setBoolean(2, isActive); stmt.execute(); where customerPhone and isActive are values you would pass in to the SELECT before its executed. Everything is fine when one day the value passed for customerPhone is NULL. For a database (Oracle is what I know most) a NULL will never be equal to NULL , the SELECT will not return rows where the customer.phone is NULL, leading to wrong results. The SELECT will have to be changed to
While working on a Legacy Application with Legacy Database design as part of fixing a bug, I thought this bug would not have ever happened if a particular column was defined as Non Nullable since this particular column was the identifier to the parent table.
Database designs I have seen tend to not constrain the data in the database. For example make the Item.ManufacturerID non-nullable and make it a foreign key to the Manufacturer table. Similarly Manufacturer.Name and Item.Rate as non-nullable columns. In any greenfield application (existing production application is a topic for another post). When you design table(s) lets say you have Item and Manufacturer table as shown below