Sql query to add not null constraint to a column

April 27, April 27, April 22, April 22, Your email address will not be published. Save my name, email, and website in this browser for the next time I comment. Skip to content Most of you must have come across the pain of adding a not null column with a default value to an existing big table.

I have populated a temp table with demo data using the below script in SQL Server Add a comment. Active Oldest Votes. Gordon Linoff Gordon Linoff 1. Have you tried overwriting the existing NULL values? Tomalak Tomalak k 64 64 gold badges silver badges bronze badges. The purpose of a constraint is to guarantee that there can be no values in a column that violate a given rule. If there was a way to add a constraint while violating values are present, constraints would be useless.

Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. I personally prefer the first way here if you have values you can put in the field manually.

That way you don't have to worry about creating and deleting a default constraint where you don't need one. The dangerous update statement you mention would be detrimental in any query. It should be simple enough to see if you have an extra column in the update statement here. You would generally be adding only a column or two at a time. If you happen to add an extra column into your update statement that doesn't belong there, in this example, then maybe you shouldn't be in charge of data in the first place.

I've never seen GO before and it appears to be not a part of the SQL specification , thus it will probably lead to failure for scripts not being executed by one of the tools that support it.

Just use semicolons? I don't recommend spreading Microsoft standards as they rarely care about any established and reasonable standard, but invent their own just to have their own invented. Apart from that, helpful answer. But I will add a note about it to my answer. Show 7 more comments.

Martin Smith Martin Smith k 81 81 gold badges silver badges bronze badges. You don't need to, but it is one option. Matt yes you do. If the table has any rows in it at all and given the condition stated "If you aren't allowing the column to be Null" then how else can the new column avoid violating the NOT NULL constraint for existing rows?

Matt - this can be a lot slower, especially in the event that the method with a default is done as a metadata only change i. It is easy to drop the default if not desired going forward. Actually I just realised this was a delayed reply by over a year to the previous comment. In that case you are allowing the column to be NULL - albeit only temporarily.