SQL Server Databases (4 Part Series)
This is a short and sweet article discussing the importance of naming database constraints. It's written from the perspective of someone mostly familiar with SQL Server databases.
Put briefly, a constraint is a database rule of some form. The various types of constraints supported by SQL Server are:
- Not Null
- Primary Key
- Foreign Key
When these constraints are added, they define some behavior internally in the database - typically enforcing rules such as referential integrity, uniqueness, or content validation. Constraints can also have impacts on indexes in the case of key constraints and index constraints.
Constraints must have a name for database reference purposes. However, the basic SQL Syntax does not require constraint names, so in cases where a constraint is added without a name, SQL Server will automatically generate a name for you.
CREATE TABLE Resumes ( ID INT NOT NULL, Name NVARCHAR(50) NULL, UNIQUE (ID) )
In this case, the
UNIQUE constraint will be added with a randomized name that is something like the following:
- Note that in this example, you would more likely use a primary key, but for purposes of illustration we're using a unique constraint *
The problem with a constraint that has a randomized name is that it becomes difficult to drop the constraint as part of a change script down the line if the production and quality assurance databases have different constraint names to represent the same type of a constraint.
Because of that, it's important to name a constraint whenever you create it. Check the syntax for each type of constraint you're working with, but our earlier example would become:
CREATE TABLE Resumes ( ID INT NOT NULL, Name NVARCHAR(50) NULL, CONSTRAINT UC_ResumeID UNIQUE (ID) )
From there, you could then drop
UC_ResumeID by name easily, regardless of which environment the script was run on.
Once again, this is a bit of a straw man example as you'd typically use a primary key, but this should illustrate the core problems that can arise without explicitly named constraints.