使用默认约束处理空值？

Question

有没有办法在传入值为NULL时插入默认值，这是我的限制条件：

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

当我运行这段代码时：

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

目前仍然会插入NULL而非默认值。

- mshwf

---

我不相信标准的 SQL 对于这种情况具有足够的表现力。你需要一个“ON INSERT TRIGGER”来在提交之前修改数据。 - Dai

@GordonLinoff：Mohamed正在寻找一种方法，使默认约束在某人将NULL值传递给该字段时应用；而不是当没有给出值时（例如，insert into Student (name) values (null) 应该生成一个具有 name='NotAvailable' 的行。 - JohnLBevan

我修改了我的问题@Gordon Linoff，请看一下。 - mshwf

Answer 1

如果我理解得正确，您想要这个：

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

实际执行此操作的方法如下：

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

（请注意，我认为这是一个不好的想法，因为NULL代表“不可用”值-应避免使用带内值来表示错误条件-尤其要避免使用字符串/文本值来表示状态值）

（这也是一个不好的想法，因为检查“NOTAVAILABLE”比检查NULL值要昂贵得多-您的表和索引将使用更多空间，并且需要更长时间才能查找和扫描）

但是如果您真的想这么做...

CREATE TRIGGER OnInsertOverrideNameIfNull ON Student INSTEAD OF INSERT AS

    INSERT INTO Student ( Foo, Bar, Name )
    SELECT Foo, Bar, ISNULL( [Name], 'NOTAVAILABLE' ) FROM [inserted]

END

- Dai

Answer 2

我不知道有什么办法可以在显式插入null时应用默认值，除非使用某种形式的解决方法。 解决方法如下：

使用触发器

（@Dai的解决方案更好; 但是，由于此选项使用了DEFAULT约束，因此介绍此替代选项。您可以选择在触发器的逻辑中编写该默认值，或将其保留为约束；从功能上讲，这没有太大区别。

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

使用存储过程

与触发器方法类似，您可以选择在@name参数为null时从插入中省略name字段，或者将默认值编码到存储过程中（这次我选择了后者，以增加变化）。
同样，您可以在插入语句中使用coalesce(@name,'NotAvailable')作为编码该值到过程的备用形式。如果您的语句与下面的简单语句相同，则可能更清晰; 但是，如果您在存储过程中执行任何其他操作，您可能希望尽早处理所有默认值。

NB：这不会直接影响对表的插入（即，存储过程比触发器更容易被绕过），但是如果您的应用程序始终使用存储过程与数据库交互，则这是更清洁的方法。

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

这将按照以下方式调用：

• 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

最后三个都具有使用@name的默认值的相同效果。

---

对于所有事情而言，上下文将决定哪种方法最有意义。

- JohnLBevan