Autogenerated constraint names in SQL Server - are they convenient, or are they a hindrance? For me, they are a hindrance.
Why should I care that SQL Server autogenerates a constraint name when I don’t explicitly specify one? Why does it matter if it names my PRIMARY KEY constraint “PK__Constrai__3214EC271FCDBCEB”, or my DEFAULT constraint “DF__Constraint__ColumnA__21B6055D”?
Why should I name each constraint myself and not rely on autogenerated constraint names?
Because it makes maintenance and database update deployments easier, simpler and in my opinion, safer.
If you want to change a constraint in the future, knowing what the name of that constraint is makes life a whole lot easier. But if you didn’t specify a name when you created the constraint originally, then you need to query the metadata views to find out what it is (sys.check_constraints, sys.default_constraints, sys.key_constraints, sys.foreign_keys in SQL Server 2005 and later). This isn’t particularly difficult, but I find it’s an unnecessary step to have to take. And often when introducing extra steps into a process, there’s the potential element of extra risk.
“PK_MyTable” makes more sense and is more memorable than “PK__Constrai__3214EC271FCDBCEB”. It does what it says on the tin.
If you deploy database updates by loading up SQL Server Management Studio, and running through a list of changes manually one by one via the UI, then you probably still don’t see the true benefit. However, if you create SQL scripts to roll out database updates, that can be tested on local systems before going to the production database server, then the benefit should be obvious. In an ideal world, you have a continuous integration server automatically building and testing code changes to a local test environment.
I like making life easier for myself which is why one the best practise approaches I follow, is to make sure I avoid SQL Server’s autogenerated constraint names.