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.

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.