Reputation: 269
I have the following setup:
CREATE TABLE dbo.Licenses
(
Id int IDENTITY(1,1) PRIMARY KEY,
Name varchar(100),
RUser nvarchar(128) DEFAULT USER_NAME()
)
GO
CREATE VIEW dbo.rLicenses
AS
SELECT Name
FROM dbo.Licenses
WHERE RUser = USER_NAME()
GO
When I try to insert data using the view...
INSERT INTO dbo.rLicenses VALUES ('test')
an error arises:
Cannot insert the value NULL into column Id, table master.dbo.Licenses; column does not allow nulls. INSERT fails.
Why doesn't the auto increment of the identity column work when trying to insert using the view and how can I fix it?
Scenario is:
The different users of the database should only be able to work with their own rows in that table. Therefore I am trying to use the view as a kind of security by checking the username. Is there any better solution?
Upvotes: 26
Views: 220157
Reputation: 559
INSERT INTO dbo.rLicenses
SELECT 'test'
Based on sqlhack in chapter "Data modifications through views"
Upvotes: 0
Reputation: 701
You just need to specify which columns you're inserting directly into:
INSERT INTO [dbo].[rLicenses] ([Name]) VALUES ('test')
Views can be picky like that.
Upvotes: 4
Reputation: 11
You have created a table with ID
as PRIMARY KEY
, which satisfies UNIQUE
and NOT NULL
constraints, so you can't make the ID
as NULL
by inserting name field, so ID
should also be inserted.
The error message indicates this.
Upvotes: 1
Reputation: 1
Inserting 'test' to name will lead to inserting NULL
values to other columns of the base table which wont be correct as Id is a PRIMARY KEY
and it cannot have NULL
value.
Upvotes: 0
Reputation: 31
Go to design for that table. Now, on the right, set the ID column as the column in question. It will now auto populate without specification.
Upvotes: 3
Reputation: 48542
What is your Compatibility Level set to? If it's 90, it's working as designed. See this article.
In any case, why not just insert directly into the table?
Upvotes: 0
Reputation: 432742
What about naming your column?
INSERT INTO dbo.rLicenses (name) VALUES ('test')
It's been years since I tried updating via a view so YMMV as HLGEM mentioned.
I would consider an "INSTEAD OF" trigger on the view to allow a simple INSERT dbo.Licenses
(ie the table) in the trigger
Upvotes: 26
Reputation: 96658
Looks like you are running afoul of this rule for updating views from Books Online: "INSERT statements must specify values for any columns in the underlying table that do not allow null values and have no DEFAULT definitions."
Upvotes: 4