Interview: How to handle SQL NOT NULL constraint on the code end

Question

I was recently asked this question in an interview an I'm having trouble formulating the question well enough to find an answer via search engine.

If my SQL database has a NOT NULL constraint placed on the "name" column, how would I be able to create that row, filling it with other data, without tripping the "name" NOT NULL constraint, assuming that you don't have the proper data to insert into the "name" field?

My off the cuff response was to insert an empty string into the "name" field, but I feel like that's too hacky. Does anyone know the proper response?

Answer 1

It's usually a best practice to insert a dummy value such as a -1 that you can easily replace later. A blank string can be more problematic in some cases. To do this you would either use a CASE WHEN statement, or ideally, an ISNULL() function which would look like this ISNULL([ColName], -1) ISNULL is probably the answer they were looking for. That would insert the data if you have it and then if it's null, it would insert a -1.

As Gordon commented, you could also use a DEFAULT value when creating the table. In my answer above, I am assuming you're working with a table that had already been created - meaning you couldn't do that without altering the table.