SQL: Update or insert of view or function 'xxx' failed because it contains a derived or constant field

In a recent forum post, the OP was asking about an issue where he had two tables “users” and “userroles” and was getting the following error when he tried to insert rows into the “userroles” table:

Msg 4406, Level 16, State 1, Line 1 Update or insert of view or function ‘UserRoles’ failed because it contains a derived or constant field.

Now the first thing that was odd about this was that the error message was clearly showing that UserRoles wasn’t a table. It’s either a view or a table-valued function (TVF). An insert into a TVF isn’t going to work, so I presume this is a view.

Now it’s saying that it contains a derived or constant field. I have no idea what the table was actually called, so let’s create one to try this:

USE tempdb;
GO

DROP VIEW IF EXISTS dbo.UserRoles;
GO
DROP TABLE IF EXISTS dbo.UserRoles_table;
GO

CREATE TABLE dbo.UserRoles_table 
(
    ID int IDENTITY(1,1) PRIMARY KEY,
    UserID uniqueidentifier NOT NULL,
    RoleID uniqueidentifier NOT NULL
)
GO

CREATE VIEW dbo.UserRoles
AS
SELECT ID, UserID, RoleID 
FROM dbo.UserRoles_table;
GO

Then an insert should be fine:

INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', '9ccc9a13-5027-48f9-9831-3e15afdb4412')
INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', 'c12f6b95-7dc7-4fa1-8f91-f3fb7da4c0c3')
GO

Running it returns the two rows inserted as expected.

How let’s add some derived things. First, I’ll add a derived column to the view and try the insert again:

ALTER VIEW dbo.UserRoles
AS
SELECT ID, UserID, RoleID,
       'User: ' + CAST(UserID AS varchar(40)) AS DoorID
FROM dbo.UserRoles_table;
GO

INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', '9ccc9a13-5027-48f9-9831-3e15afdb4412')
INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', 'c12f6b95-7dc7-4fa1-8f91-f3fb7da4c0c3')
GO

Adding a derived column in the view works just as expected. What about adding it to the table instead?

ALTER TABLE dbo.UserRoles_table
ADD DoorID AS 'User: ' + CAST(UserID AS varchar(40));
GO

ALTER VIEW dbo.UserRoles
AS
SELECT ID, UserID, RoleID 
FROM dbo.UserRoles_table;
GO

INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', '9ccc9a13-5027-48f9-9831-3e15afdb4412')
INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', 'c12f6b95-7dc7-4fa1-8f91-f3fb7da4c0c3')
GO

I’ve set the view so that it doesn’t use that derived column, and all is still good. But what if the view references the derived column?

ALTER VIEW dbo.UserRoles
AS
SELECT ID, UserID, RoleID, DoorID 
FROM dbo.UserRoles_table;
GO

INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', '9ccc9a13-5027-48f9-9831-3e15afdb4412')
INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', 'c12f6b95-7dc7-4fa1-8f91-f3fb7da4c0c3')
GO

And that’s all good too. So what does that leave us?

Well a common view definition that I often come across is where the view returns data from a table, along with a constant row. Perhaps it’s a “superuser” or something in this case. So let’s try that:

ALTER VIEW dbo.UserRoles
AS
SELECT ID, UserID, RoleID 
FROM dbo.UserRoles_table
UNION ALL 
SELECT 1, '9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', 'c12f6b95-7dc7-4fa1-8f91-f3fb7da4c0c3';
GO

INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', '9ccc9a13-5027-48f9-9831-3e15afdb4412')
INSERT dbo.UserRoles (UserID, RoleID) 
VALUES('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', 'c12f6b95-7dc7-4fa1-8f91-f3fb7da4c0c3')
GO

And there’s the error:

Msg 4406, Level 16, State 1, Line 78 Update or insert of view or function ‘dbo.UserRoles’ failed because it contains a derived or constant field.

You can’t insert through a view when you have only this type of setup.

2020-01-23