Check Constraints To Block Leading And Trailing Spaces

Louis Davidson shows how to use check constraints to block people from inserting records with leading or trailing spaces:

Then, let’s say the requirements are as follows:

1. No values that are either empty or only spaces
2. No leading spaces
3. No trailing spaces
4. Allow NULL if column allows NULL

Let’s look at how we could implement all of these independently, as there certainly are cases where you may wish to allow any or all of the situations in a column.

Click through for the scripts, as well as a time comparison to see how much overhead you’re adding.

Related Posts

Finding System-Generated Constraint Names

Michael J. Swart has a script which helps you find system-generated constraint names: Names for constraints are optional meaning that if you don’t provide a name when it’s created or cannot afford one, one will be appointed to you by the system. These system provided names are messy things and I don’t think I have […]

Read More

Unique Indexes Versus Unique Constraints

Greg Low argues that you should create unique constraints instead of unique indexes whenever possible: The CREATE INDEX statement is used to do exactly what its name says, it creates an index. But when you say CREATE UNIQUE INDEX, you are doing more than that; you are enforcing a business rule that involves uniqueness. I […]

Read More


March 2018
« Feb Apr »