Sql-server – Defining a column as nullable in a view (but not in the referenced table)

sql serversql-server-2012t-sql

My company is working on implementing a new HR application that will unify our existing HR processes. We've run into a bit of a sticking point, though; according to business rules, the EmployeeID and SupervisorID columns in the Employee table cannot be null. However, the application requires these columns to be nullable, and won't synchronize the columns while they're set to disallow null.

The proposed solution was to create a view where the schema appeared to allow null values in a column, while leaving the table alone to prevent them from being entered into the data. I'm not entirely convinced this is possible, and I haven't been able to find a way to do it. Is this possible? If not, what is the best way to handle this situation?

Best Answer

Yes, it's possible:

select Id, -- Remains as in table
  cast(MandatoryIntCol as int) as [MandatoryIntCol], -- Makes nullable
  isnull(OptionalIntCol, 0) as [OptionalIntCol] -- Makes mandatory
from dbo.MyTable;

It's worth noting that there are some restrictions with this approach:

  1. The changed columns are not writeable in the view, so if the OP's application tries to write to them, it would fail.

  2. The application must not try to distinguish between tables and views. Most apps do not, but annoyingly, some product applications do.