CODEWITHSUNDEEP
sqlsql-serversql-insert
AJ_1125
AJ_1125

Reputation: 13

How can I avoid the following error while trying to insert values into sql?

I'm trying to insert one-to-many values in SQL. I have two tables: users and userroles.

I'm trying to insert several userroles for each user, where multiple roles already exist for these users. I'm using an insert statement but getting an error (shown below). I understand that the userid is likely my problem as it comes from the users table, but I don't know how to solve the issue... I thought the table would auto-insert an ID (the UserRoles table only contains 3 columns: ID, UserID and RoleID)

Syntax for adding two RoleID values for a given user:

insert into UserRoles (userid, roleid) 
values('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', '9ccc9a13-5027-48f9-9831-3e15afdb4412')
insert into UserRoles (userid, roleid) 
values('9481EFEB-D36B-4381-8E9C-73EBC3BAC12D', 'c12f6b95-7dc7-4fa1-8f91-f3fb7da4c0c3')

Error:

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.

Upvotes: 0

Views: 2145

Answers (1)

Greg Low
Greg Low

Reputation: 1586

what you've asked is a bit confusing. You mention you have two tables: users and userroles, but the update to userroles is clearly showing it's a view. I suspect there must be something else in your view definition, apart from just those three columns. Here's an example:

USE tempdb;
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

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

That works just fine. However if I add something else like this to the view definition, to perhaps have a special admin role or similar:

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

Now notice when I try to insert again:

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 get that same error: Msg 4406, Level 16, State 1, Line 32 Update or insert of view or function 'UserRoles' failed because it contains a derived or constant field.

I hope that helps you find the issue.

Upvotes: 2

Related Questions