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.
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
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 = 1exec sp_StudentCRUD_Insert @name=null, @classId = 2exec sp_StudentCRUD_Insert @name=default, @classId = 3exec sp_StudentCRUD_Insert @classId = 4The 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.
ON INSERT TRIGGERto modify the data before it's committed.insert into Student (name) values (null)should result in a row withname='NotAvailable'