You cannot insert the value NULL into the column 'sdept', the table 'XSGL.dbo.student'; the column does not allow Null values. INSERT failed

Question

There are my problems on SQL Server. I have a Database named XSGL to management the students' information.

First, I create a view named IS_Student on table student.

CREATE VIEW IS_Student
AS SELECT sno, sname, ssex, sage
FROM student
WHERE sdept = 'IS'
WITH CHECK OPTION;

Then I want to insert a student by the view.

INSERT INTO IS_Student 
VALUES('200215129', '赵新', '男', 20) ;

But it has an error :

Msg 515, Level 16, State 2, Line 1
不能将值 NULL 插入列 'sdept'，表 'XSGL.dbo.student'；列不允许有 Null 值。INSERT 失败。
语句已终止。

I translate it to english.

You cannot insert the value NULL into the column 'sdept', the table 'XSGL.dbo.student'; the column does not allow Null values. INSERT failed.
The statement has been terminated.

The view IS_Student was created on students whose sdept is 'IS', and I also create it with WITH CHECK OPTION . Why does the error tell that I can't insert the value NULL into the column 'sdept'.

Sorry for my poor Endlish. Thanks in advance.

Answer 1

sdebt is not part of your view and it is a not nullable column in your underlying table. As it's not part of your view, or your INSERT statement, it will try to insert NULL into it - which will make it fail.

Answer 2

The condition is part of the view, but the insert is executed on the underlying table.

You need to create an Insert Trigger on the view which handles the default value for the view condition:

CREATE TRIGGER TR_Ins_IS_Student 
ON dbo.IS_Student
INSTEAD OF INSERT 
AS
BEGIN
    INSERT INTO dbo.Student (sno, sname, ssex, sage, sdept)
    SELECT sno, sname, ssex, sage, 'IS'
    FROM inserted
END