Alter column null and default value on existing

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Alter column null and default value on existing

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-09-16 : 05:51:17

Hi.
I have a table that already have some data. I want to alter a column ,make it not null give a default value and then drop the CONSTRAINT. The problem is that since the table has data(NULL rows if this column) will give error:
Cannot insert the value NULL into column 'lok', column does not allow nulls. UPDATE fails.


ALTER TABLE zhokev 
ADD CONSTRAINT loknotnull DEFAULT '1' FOR lok
GO
alter table zhokev 
alter column lok nvarchar(200) not null
GO
ALTER TABLE zhokev
DROP CONSTRAINT loknotnull

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2014-09-16 : 05:58:40

I guess this: ?



ALTER TABLE zhokev 
ADD CONSTRAINT loknotnull DEFAULT '1' FOR lok
GO
Update zhokev set lok = '1' where lok is NULL
alter table zhokev 
alter column lok nvarchar(200) not null
GO

ALTER TABLE zhokev
DROP CONSTRAINT loknotnull

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2014-09-16 : 07:21:24

You really do not need the constraint to change the null values to your default.
Just update the table and then alter to not null

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources