If you’ve ever tried to change a default value of a SQL constraint you’ve probably noticed that you have to drop the constraint, and then re-create the constraint with a new default value. However, what if you found an auto-named SQL constraint, and you started wondering how your SQL script will work across different environments with different databases…
The solution would be querying master database (assuming you have access to it) and selecting a column’s constraint’s name (similar to table names) into a variable, generating inline SQL script to drop the constraint and executing it, and then creating a new constraint with the new desired default value. Let’s do this step by step with sample data, scripts, and screenshots.
First, let’s create a new database called TestDatabase using SQL Management Studio’s UI. Then run following script to create Users table with a default age value of 999, and insert a test record that contains a name value only, hence a default age value should be inserted.
CREATE TABLE Users ([Name] NVARCHAR(100), Age INT DEFAULT(999)) GO INSERT INTO Users (Name) VALUES ('Ignas') GO
Now the table schema and data should look following.
To update the default age value in Users table to let’s say 0, run the following SQL code snippet.
And after inserting one more test record and refreshing constraints list in MS SQL Studio (it doesn’t update automatically), the table schema and data should look like following.
The auto-named SQL constraint has been re-created (random part of the name is different), and the default age value has changed to 0 as well. All set!
Of course, an easier option to maintain constraints e.g. drop and update, would be to assign a specific name to the constraint (MS Docs example), hence avoid the need to query master database to get the auto-generated name. You could simply drop the constraint by it’s name, and re-creating using the same name.