April 18, 2014 at 8:59 am
I have a db1.tPersonJobHist where I have two columns
[WorkflowCoordinatorFlag] [dbo].[shrsFlag] NOT NULL,
[HRBusinessPartnerCode] [dbo].[shrsCode] NULL,
Our developer mistakenly made HRbusinesspartnercode field as nullable.
He now wants to change it to Not Nullable.
So there were 4 rows where the values were not null(not sure how)
We edited those rows and changed those values to NUll.
Now we have no Nulls in that column.
So we brought up the table designer and made the change but got the following error
'tPersonJobHist' table
- Unable to modify table.
Cannot insert the value NULL into column 'HRBusinessPartnerCode', table 'BD6578.dbo.Tmp_tPersonJobHist'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Question is why wouldn't it let me alter the table design. and what is tmp_tpersonjobhist.?
How can I make this column Not-nullable.
Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA
April 18, 2014 at 9:35 am
It seems like you mixed the NULL and NOT NULL terms in your post... (e.g. 4 rows were not null, we changed it to NULL)
What is the result of the following query?
SELECT * FROM db1.tPersonJobHist WHERE [HRBusinessPartnerCode] IS NULL
Instead of using the table designer you might want to apply the SQL code directly:
ALTER TABLE db1.tPersonJobHist
ALTER COLUMN [HRBusinessPartnerCode] [dbo].[shrsCode] NOT NULL
Behind the scenes SSMS will create a temp table called tmp_tpersonjobhist with the new column definition and copy all current ros into that table.
Then it'll dorp the current table and rename the tmp table to the original name.
The error message you receive indicate there are still rows with NULL values in your original table.
As a side note: I hope there are good reasons to use custom data types... And you might want to consider adding a clustered index to your table, too.
April 18, 2014 at 9:47 am
I'd do as Lutz suggested.
As a side note, never commit changes in the Table Designer. It makes it easy to change things, add indexes, set primary keys, etc, and I use it often, but always click the "Script" button and get the actual code. You can see what's happening and decide if you want to do it.
You can also then put that in VCS 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply