December 3, 2013 at 3:27 am
Hello SQL Guru's
Im trying to add a column in a table but i get the following error;
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'incident_id', table 'DWH.dbo.incident'; column does not allow nulls. INSERT fails.
I use the following syntax to add the column ( I copy a previous column from a other table ) ;
GO
INSERT INTO DWH.DBO.incident (incident_ref)
SELECT incident_ref
FROM incident;
GO
I create column as follow;
alter table incident
add incident_ref varchar(25)
See picture1 how my table original table looks
and picture2 how my table look like now
December 3, 2013 at 4:18 am
Do I forget something to tell ??
December 3, 2013 at 4:28 am
It looks like your insert statement is failing as that's the primary key for the table and it will not allow NULL values.
December 3, 2013 at 5:42 am
Do you think if I change the Primary table to Null will it fix it ??
And can I make it agian NOT NULL
December 3, 2013 at 5:48 am
No I don't. I think you need to revisit what you are trying to do.
Perhaps you should be UPDATE'ing that field? Your INSERT statement implies you just want to insert 1 column into the table, which is just going to keep failing as you're missing all other columns with NOT NULL constraints.
December 3, 2013 at 6:24 am
You're inserting a value into incident_ref.
You're primary key (incident_id) is not set as an identity so you need to insert a unique value since you cannot have a NULL primary key.
December 3, 2013 at 6:25 am
Knight (12/3/2013)
No I don't. I think you need to revisit what you are trying to do.Perhaps you should be UPDATE'ing that field? Your INSERT statement implies you just want to insert 1 column into the table, which is just going to keep failing as you're missing all other columns with NOT NULL constraints.
Looking at the screen shot there are no NOT NULL columns other than the primary key.
December 3, 2013 at 6:34 am
Dooh! Pict 1 old! Pict 2 new!
anyway, still think the inserts wrong unless you just want everything else empty. If it was an identity column though surely you wouldn't receive this error.
December 3, 2013 at 6:41 am
Knight (12/3/2013)
If it was an identity column though surely you wouldn't receive this error.
Eureka! 😀
December 3, 2013 at 7:07 am
MysteryJimbo (12/3/2013)
You're inserting a value into incident_ref.You're primary key (incident_id) is not set as an identity so you need to insert a unique value since you cannot have a NULL primary key.
What kind of query can I use in this situation;
Indetity ON Table incident
alter table incident_ref
add column ...
Give me some examples
December 3, 2013 at 7:59 am
karim.boulahfa (12/3/2013)
MysteryJimbo (12/3/2013)
You're inserting a value into incident_ref.You're primary key (incident_id) is not set as an identity so you need to insert a unique value since you cannot have a NULL primary key.
What kind of query can I use in this situation;
Indetity ON Table incident
alter table incident_ref
add column ...
Give me some examples
It is not clear what you are trying to do here. Are you wanting to allow inserting of identity values?
http://technet.microsoft.com/en-us/library/ms188059.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
December 3, 2013 at 8:05 am
karim.boulahfa (12/3/2013)
MysteryJimbo (12/3/2013)
You're inserting a value into incident_ref.You're primary key (incident_id) is not set as an identity so you need to insert a unique value since you cannot have a NULL primary key.
What kind of query can I use in this situation;
Indetity ON Table incident
alter table incident_ref
add column ...
Give me some examples
That depends on what Incident_id is. Is it a natural primary key or is it simply intended to be an incremental number?
If its meant to be an incremental number and has no bearing on the data, I would recreate table with the primary key set as IDENTITY(1,1) (or some other setting, such as a negative integer. I suggest you do research).
Also, this query looks like it could be wrong in someway as you are only moving one column (possibly into another database). Can you confirm what you are trying to do?
INSERT INTO DWH.DBO.incident (incident_ref)
SELECT incident_ref
FROM incident;
GO
It should be this
INSERT INTO DWH.DBO.incident (incident_id, incident_ref)
SELECT incident_id, incident_ref
FROM incident;
GO
/*Or maybe */
USE DWH
GO
SET IDENTITY_INSERT dbo.incident ON
GO
INSERT INTO DBO.incident (incident_id, incident_ref)
SELECT incident_id, incident_ref
FROM /*insert source db name and schema*/incident;
GO
SET IDENTITY_INSERT dbo.incident OFF
GO
December 3, 2013 at 10:33 am
MysteryJimbo (12/3/2013)
karim.boulahfa (12/3/2013)
MysteryJimbo (12/3/2013)
You're inserting a value into incident_ref.You're primary key (incident_id) is not set as an identity so you need to insert a unique value since you cannot have a NULL primary key.
What kind of query can I use in this situation;
Indetity ON Table incident
alter table incident_ref
add column ...
Give me some examples
That depends on what Incident_id is. Is it a natural primary key or is it simply intended to be an incremental number?
If its meant to be an incremental number and has no bearing on the data, I would recreate table with the primary key set as IDENTITY(1,1) (or some other setting, such as a negative integer. I suggest you do research).
Also, this query looks like it could be wrong in someway as you are only moving one column (possibly into another database). Can you confirm what you are trying to do?
INSERT INTO DWH.DBO.incident (incident_ref)
SELECT incident_ref
FROM incident;
GO
It should be this
INSERT INTO DWH.DBO.incident (incident_id, incident_ref)
SELECT incident_id, incident_ref
FROM incident;
GO
/*Or maybe */
USE DWH
GO
SET IDENTITY_INSERT dbo.incident ON
GO
INSERT INTO DBO.incident (incident_id, incident_ref)
SELECT incident_id, incident_ref
FROM /*insert source db name and schema*/incident;
GO
SET IDENTITY_INSERT dbo.incident OFF
GO
Yes I confirm. That is the only thing what Im doing.
I try the 2 qeurys but I getting this error;
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'inc_status', table 'DWH.dbo.incident'; column does not allow nulls. INSERT fails.
The statement has been terminated.
December 4, 2013 at 1:36 am
Well your table definition has changed then as this doesn't match the 2nd picture you posted. i.e. that column should allow NULLs according to picture, but it doesn't...
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply