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?

up vote 7 down vote accepted

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.

  • This solution works fine!. Thanks. – mggSoft Oct 13 '16 at 14:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.