SQL Server: Allow Only Alpha Numeric Characters to a Column

Here is how to restrict non alpha numeric characters to a column in SQL Server

--Create Demo Table
 CREATE TABLE AlphaNumDemo
 (
 DemoCode NVARCHAR(100)
 )
 GO

--Restrict Special Characters
 ALTER TABLE AlphaNumDemo
 ADD CONSTRAINT CHK_AllowAplhaNumericCharactresOnly
 CHECK (DemoCode NOT LIKE '%[^a-zA-Z0-9 ]%')
 GO

--Insert Test Data
 INSERT INTO AlphaNumDemo VALUES ('demo1')
 INSERT INTO AlphaNumDemo VALUES ('Demo1')
 INSERT INTO AlphaNumDemo VALUES ('Demo 1')
 INSERT INTO AlphaNumDemo VALUES ('Demo-1')
 INSERT INTO AlphaNumDemo VALUES ('#1Demo')
 GO

--Check Data
 SELECT DemoCode FROM AlphaNumDemo
 GO

--Cleanup
 DROP TABLE AlphaNumDemo
 GO

In the above demonstration observe the records with special characters will not be inserted to the table.

CHECK constraint with User Defined Function in SQL Server

This post describes how to use result of an user defined function with CHECK constraint in SQL Server.  For demonstration, considered a situation where it is not  allowed to insert or update records where calculated age of a person is less than 18 years as per his/her Date of Birth.

So for this first we need to create the function before creating the CHECK constraint. Here is a function which will return age as per the date of birth provided.

/*
This function will take Date Of Birth as input parameter,
and returns Age in Years to the caller
*/
CREATE FUNCTION [dbo].[fnGetAge](@DateOfBirth DATETIME)
RETURNS SMALLINT
AS
BEGIN
DECLARE @Age SMALLINT
SET @Age =(DATEDIFF(YY, @DateOfBirth, GETDATE())-
(CASE
WHEN GETDATE() >= DATEADD(YY, DATEDIFF(YY, @DateOfBirth, GETDATE()), @DateOfBirth) THEN 0
ELSE 1
END))
RETURN @Age
END;
GO

Now create a table where CHECK constraint will refer to this function to check if the age of the person meets the required criteria or not (minimum 18 Years in this case).

--Create Customer table
CREATE TABLE Customers
(
CustID INT IDENTITY(1,1) NOT NULL,
CustName VARCHAR(100) NOT NULL,
DateOfBirth DATETIME NOT NULL,
Email VARCHAR(100),
CONSTRAINT pkCustomers PRIMARY KEY(CustID),
--Calculate & check if age of customer is atleast 18 Years
CONSTRAINT chkCheckAge CHECK(dbo.fnGetAge(DateOfBirth) >= 18)
)
GO

--Populate table with some sample data.
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('ABC','19810726','abc@cust.info'),
('XYZ','19840510','xyz@cust.info'),
('MNO','19720417','mno@cust.info')
GO
--Result Message
--(3 row(s) affected)

Now try to insert a record where calculated age is less than 18 years and see what happens.

--Try to insert a record where Age less than 18 Years(as per provided Date of Birth)
INSERT INTO Customers(CustName, DateOfBirth, Email)
VALUES ('TEST','20010315','test@cust.info')
GO

--Error Message
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "chkCheckAge".
The conflict occurred in database "SQLJourney", table "dbo.Customers", column 'DateOfBirth'.
The statement has been terminated.

As the age does not meet the required criteria in defined CHECK constraint, it doesn’t allow to insert this record to the table.

Note:

CHECK constraint evaluates the provided expression while UPDATE operation as well.

You cannot DROP the function till the table (which refers to that function) exists in the database.

UNIQUE Key Constraint with Multiple NULL values in SQL Server

This was a really interesting situation when we development team members were discussing over this topic. It was a situation where it was not possible to allow multiple NULL values into a column with UNIQUE Key defined in a table. Here I will give the problem first then we will go with the alternate solution for this.

Problem:

In SQL Server it is not allowed to insert multiple NULL values into a UNIQUE Key column. Below is the situation:

--Create employee table
CREATE TABLE EMPLOYEE
(EMPLOYEEID INT NOT NULL
CONSTRAINT PK_EMPLOYEE PRIMARY KEY CLUSTERED
,FNAME VARCHAR(100)
,LNAME VARCHAR(100)
,UNIQUEID INT UNIQUE)
GO
--Insert data values (used row constructor)
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME, UNIQUEID)VALUES
(1,'RAHUL','MISHRA','112342115'),
(2,'SHEEL','KURANI','1725421455'),
(3,'SHEETAL','BAJAJ','1423721455'),
(4,'KAUSHIK','NARANG','1123721955'),
(5,'ADRIAN','THOULISS','1452342145')
GO
--Insert data value with NULL values to UNIQUE key column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(6,'SAUGAT','KIOHLI')
--See below the result 
SELECT EMPLOYEEID, FNAME, LNAME, UNIQUEID FROM EMPLOYEE
GO

When I try to enter another record with NULL value to UNIQUEID column, it does not allow me to do that and throws an error.

--Insert data value with NULL values to UNIQUE key column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(7,'VAID','GURU')

--ERROR MESSAGE
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ__EMPLOYEE__04FF5B33023D5A04'.
Cannot insert duplicate key in object 'dbo.EMPLOYEE'.
The statement has been terminated.

Solution:

Here is the alternate solution for the above situation. Instead of define an UNIQUE Key to UNIQUEID column I will create another COMPUTED Column with UNIQUE Key. See how it is done:

--ALTERNATE SOLUTION
--Drop the UNIQUE key
ALTER TABLE EMPLOYEE
DROP CONSTRAINT UQ__EMPLOYEE__04FF5B33023D5A04

--Add a computed column with UNIQUE key
ALTER TABLE EMPLOYEE
ADD ALTUNIQUEID AS CASE WHEN UNIQUEID IS NULL THEN EMPLOYEEID ELSE UNIQUEID END
GO
ALTER TABLE EMPLOYEE
ADD CONSTRAINT UKey_QNIQUEID UNIQUE (ALTUNIQUEID)
GO

--Now insert multiple records with NULL to UNIQUEID column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(7,'VAID','GURU'),(8,'TARUN','KISHORE')
GO
--See below the result
SELECT * FROM EMPLOYEE
GO

Your valuable comments are most appreciated.