As part of a demo recently I was adding a default value to a new column with a simple DEFAULT and a value. Under the covers this creates a constraint, however, I want to ensure this is named explicitly and not auto generated. This post shows how to do this.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Setup
Let’s create a simple table like this one:
CREATE TABLE dbo.OrderHeader (
OrderHeaderID INT NOT NULL CONSTRAINT OrderHeaderPK PRIMARY KEY,
OrderDate DATETIME,
CustomerID INT
)
GO
Now I want to add a Created column to the table, with a default value of the current date and time. I decide to do this with an ALTER TABLE statement. In the past, I’ve done this with this code:
ALTER TABLE dbo.OrderHeader
ADD Created DATETIME DEFAULT GETDATE()
The problem is this creates a constraint with a system generated name. If I deploy this code to different systems, I get different names. If I need to change the constraint or drop it, I have to query to find the name as it isn’t explicit. You can see this below.
What I’d rather do is have a named constraint that makes sense to me. Let’s drop this column and do a better job. However, I cant’ just drop the column because I need to drop the constraint and that means I need to get the name.
That’s the problem I’m trying to solve. Here is what I need to do.
Now that I’ve dropped this, let’s add it back with an explicit name. This is simple SQL, and easy to add, just like I can do for Primary Keys. We’ll add a CONSTRAINT keyword and name before the default.
ALTER TABLE dbo.OrderHeader
ADD Created DATETIME CONSTRAINT df_OrderHEader_Created_Getdate DEFAULT GETDATE()
GO
When I run this, now I see a named constraint.
Note that I’ve named this for the column as if I need similar constraints in this table, they need to be uniquely named. This is in the database, not the table, as all constraints are stored in sys.default_constraints.
Do this and your database deployments go easier, especially across multiple systems.
SQL New Blogger
This is a simple thing, but it’s a good coding practice and better software engineering than allowing the system to name things. I explained how to do this and related this to a real issue in database development: deployments.
This post took me about 10 minutes, and it would likely take you about the same to start showcasing your knowledge. In today’s world, maybe you use AI to help you solve this problem and showcase that skill.
