How to ensure that the mobile field must contain a unique value?

To ensure that the mobile field must contain a unique value -

Have you considered using duplicate management (i.e., create a matching rule and a duplicate rule) instead of a validation rule? IMHO that feels better for phone numbers since it would also allow you to change the behaviour easily from completely blocking the creation of a new record with a non-unique phone number to just warning the user about a possible duplicate and leaving the choice on how to proceed to the user. After all, phone numbers might not be fully unique (esp. if land lines are included or if you consider a long duration where a cell number might be reassigned to a different subscriber). Since you can't use VLOOKUP or the unique attribute on data fields of type phone, another option would be to change the data type to text -- but keep in mind that you lose the default functionality of a phone field with this (e.g., you'd have to create a validation rule with REGEX to enforce the format to a legal phone number).

To ensure that the Mobile field in a SQL Server table contains unique values, you can create a unique constraint or a unique index on that field. This will enforce the rule that no two rows in the table can have the same value in the Mobile column.

Using a Unique Constraint

A unique constraint ensures that the values in a column (or a combination of columns) are unique across the table. Here’s how you can add a unique constraint to the Mobile column:

ALTER TABLE Employees

  ADD CONSTRAINT UQ_Mobile UNIQUE (Mobile);

Using a Unique Index

A unique index also ensures that the values in the column are unique. In SQL Server, a unique index is essentially the same as a unique constraint, but you create it using the CREATE UNIQUE INDEX statement. Here’s how you can do it:

  CREATE UNIQUE INDEX UQ_Mobile ON Employees (Mobile);

Full Example

Let’s put it all together. Suppose you have a table named Employees and you want to ensure that the Mobile field is unique:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FullName NVARCHAR(100),
    Mobile VARCHAR(15)
);

Add the Unique Constraint

ALTER TABLE Employees

  ADD CONSTRAINT UQ_Mobile UNIQUE (Mobile);

Handling Existing Data

If your table already contains data, and you need to enforce the uniqueness of the Mobile column, you must first ensure that there are no duplicate values. Here’s how you can identify and handle duplicates:

Find Duplicates

SELECT Mobile, COUNT(*)
FROM Employees
GROUP BY Mobile
HAVING COUNT(*) > 1;

Remove or Update Duplicates

You need to decide how to handle duplicates, either by removing or updating them to ensure uniqueness. For example, to remove duplicates and keep only one instance of each:

WITH DuplicateCTE AS (
    SELECT 
        EmployeeID,
        ROW_NUMBER() OVER (PARTITION BY Mobile ORDER BY EmployeeID) AS rn
    FROM Employees
)
DELETE FROM DuplicateCTE
WHERE rn > 1;

Add the Unique Constraint

After ensuring all values are unique, you can safely add the unique constraint:

  ALTER TABLE EmployeesADD CONSTRAINT UQ_Mobile UNIQUE (Mobile);

Summary

To ensure that the Mobile field in a SQL Server table contains unique values, you can:

Add a unique constraint to the column using the ALTER TABLE statement.

Create a unique index on the column using the CREATE UNIQUE INDEX statement.

Before adding the constraint or index, make sure there are no duplicate values in the column to avoid errors.