Using default constraint with null values?

Question

Is there a way to insert default value if the coming value is NULL , this is my constraint:

ALter table Student
add Constraint DF_Name Default 'NOTAVAILABLE' For [Name]

When I run this:

Insert into Student (Name,ClassId)values (NULL,1)

it still insert NULL instead of the default value.

• Dai: I don't believe standard SQL is expressive enough for this scenario. You would need an ON INSERT TRIGGER to modify the data before it's committed.
• JohnLBevan: @GordonLinoff: Mohamed is looking for a way to have the default constraint apply when someone passes a value of NULL to that field; as opposed to when a value is not given (e.g. insert into Student (name) values (null) should result in a row with name='NotAvailable'
• mshwf: I amended my question @Gordon Linoff please have a look

Answer 1

If I understand you, you want this:

INSERT INTO Student ( Foo, Bar, Name ) VALUES ( 'Baz', 'Qux', NULL )

to actually do this:

INSERT INTO Student ( Foo, Bar, Name ) VALUES ( 'Baz', 'Qux', 'NOTAVAILABLE' )

(Note that I think this is a bad idea because NULL is meant to represent "NOTAVAILABLE" values - you should avoid in-band values to represent error conditions - and especially avoid string/text values to represent status values)

(It's also a bad idea because checking for "NOTAVAILABILE" is considerably more expensive than checking for NULL values - your tables and indexes will use more space and take longer to seek and scan)

But if you really want to do this...

CREATE TRIGGER OnInsertOverrideNameIfNull ON Student INSTEAD OF INSERT AS
 INSERT INTO Student ( Foo, Bar, Name )
 SELECT Foo, Bar, ISNULL( [Name], 'NOTAVAILABLE' ) FROM [inserted]
END

Answer 2

I'm not aware of a way to have default values applied when null is explicitly inserted without some form of workaround. Workarounds are:

Use Triggers

(@Dai's solution is better here; however presenting this alternate option as this makes use of your DEFAULT constraint. Whether you'd rather code that default in your trigger's logic, or hold it as a constraint is an implementation choice; it makes little difference functionally.

create trigger trg_student_insert
on Student
instead of insert
as
begin
 insert into Student (Name, ClassId) 
 select i.name, i.ClassId
 from inserted i
 where name is not null
 insert into Student (ClassId) 
 select i.ClassId
 from inserted i
 where name is null
end

Use Stored Procedures

Similar to the trigger approach; again you have the option to either miss out the name field from the insert when the @name parameter is null, or to code a default value into the stored procedure (this time I've done the latter, for variety).
Equally you could use coalesce(@name,'NotAvailable') in your insert statement as an alternate form of coding the value into the procedure. That may be cleaner if your statement's as simple as below; but if you're doing anything more in your stored proc you may want to handle all defaulting early on.

NB: This won't impact inserts directly to the table (i.e. the stored proc is more easily bypassed than the trigger), but if your application will always use stored procedures to interact with your database, this is the cleaner approach.

create procedure sp_StudentCRUD_Insert
(
 @name nvarchar(32) = null
 , @classId int
)
as
begin
 set nocount on
 if (@name is null) set @name = 'NotAvailable' 
 insert into Student (name, classId)
 values (@name, @classId)
 select scope_identity()
end

This would be called as follows:

• exec sp_StudentCRUD_Insert @name='Mohamed', @classId = 1
• exec sp_StudentCRUD_Insert @name=null, @classId = 2
• exec sp_StudentCRUD_Insert @name=default, @classId = 3
• exec sp_StudentCRUD_Insert @classId = 4

The last 3 all have the same effect of using the default value for @name.

---

As with everything, context will drive which of the approaches makes the most sense.