Tag : constraint

Add constraint without checking existing data

In this post “Add constraint without checking existing data” we are going to learn how we can add a constraint on a column which already has invalid data. We create constraints to automatically enforce the integrity of the database. Using constraints we can define the rules for valid set of values for a given column. Let’s learn how we can instruct SQL Server to ignore check on the existing data during constraint creation on a column.

We have an employee table with columns EmpId, EmpName and ContactNumber. Column ContactNumber holds few invalid values, as we don’t had a constraint on this column. Now, we want to accept only valid values in the contact number column being validated with the custom business logic, but we don’t want to remove or modify the already existing data in the column. We need to put a constraint for coming data and not for existing … More

Allow only alphanumeric characters in a column 2

One of my colleague asked me a question that how to “allow only alphanumeric characters” in a column. Below is the exact question:

How can i stop accepting any non-alphanumeric characters during DML operations in a column of a table?

To achieve this, we can follow below steps. There may be more different ways other than these;

  1. Create a table
  2. Create a scalar function which returns true or false by checking non-alphanumeric characters (Using scalar function as an example so that in case of complex logic it can be implemented inside this function.)
  3. Create a check constraint on table and call this created function to check that column has only alphanumeric values

Some other way, we can also create an “Instead Of Trigger” and write a logic to check alphanumeric values for that column which I will not cover in this post.

Now let’s brief me each step mentioned above:… More