Creating Constraint on Table to check for data integrity

Creating Constraint on Table to check for duplicate values

Sometime we are asked to make sure that there is no duplicate data in our table based on certain columns combination. There are many ways to do this.

The first thing that will come to our mind is TRIGGER.

Well Trigger are good but its not always  best solution, particularly in our case here.

So let see how we can do it.

Create a table and insert some data.



CREATE TABLE [dbo].[Product](
[ProductID] [int],
[ProductName] varchar(100) NOT NULL,
[IsActive] Bit NOT NULL,
[RetiredDate] Datetime2,
[MinPrice] Float,
[MaxPrice] Float
)

Just to show how constraint works, I am not adding any Primary Key or Foreign Key relation with other tables.

Let's insert some data in this table.



  Insert INTO Product ([ProductID],[ProductName] ,[IsActive],[MinPrice] ,[MaxPrice])
      VALUES (101, 'Mountain-Bike', 1, 100.00, 500.00), (102, 'Mountain-rugged',1,299.99,999.99), (103, 'Mountain', 0, 199.99,399.99)

Check to make sure you have data in table you just inserted.


SELECT * FROM Product

Now let's say that our ProductID + ProductName+ IsActive has to be unique row in our table. So in this case based on this, there can be only two combination (0 or 1 for active and inactive values)

Suppose we do not want user to enter a value of above combination because we are considering this to be a unique values.



"101--Mountain-Bike--1"

Right now with no constraint, we will be able to enter this value.


 Insert INTO Product ([ProductID],[ProductName] ,[IsActive],[MinPrice] ,[MaxPrice])
      VALUES (101, 'Mountain-Bike', 1, 59.99, 199.00)

Here, I have just changed MinPrice and MaxPrice but productID, productName and IsActive are same, which should not be allowed if we want to maintain data consistency.


To prevent these type of data, we can create trigger based on unique combination of three columns where we want to maintain data integrity.


To do so, we have to remove last inserted row from our table. Let's go and remove that row.

Method 1:


   
ALTER TABLE dbo.Product
ADD CONSTRAINT unique_PID_PNAME_Active UNIQUE([ProductID],[ProductName] ,[IsActive])

Method 2:


CREATE UNIQUE INDEX uq_product
  ON dbo.Product([ProductID],[ProductName] ,[IsActive]);

Method 3: which is trigger (not recommended but you can still do it)

CREATE TRIGGER dbo.BlockDuplicatesproduct ON dbo.product

INSTEAD OF INSERT

AS

BEGIN

SET NOCOUNT ON;

IF NOT EXISTS (

SELECT 1

FROM inserted AS i

INNER JOIN dbo.product AS t ON i.productID = t.productID

AND i.[ProductName] = t.[ProductName]

)

BEGIN

INSERT dbo.product (

[ProductID]

,[ProductName]

,[IsActive]

,[RetiredDate]

,[MinPrice]

,[MaxPrice]

)

SELECT [ProductID]

,[ProductName]

,[IsActive]

,[RetiredDate]

,[MinPrice]

,[MaxPrice]

FROM inserted;

END

ELSE

BEGIN

PRINT N'';

END

END

GO;

Now test and see it works for you or not.