CODEWITHSUNDEEP
sql-serverconstraintsunique
Rumpelstinsk
Rumpelstinsk

Reputation: 3241

SQL Server 2008: Unique constraint for values non-related with columns

I have a simple problem. How can I add a unique constraint for a table, without relating the values to their columns? For example, I have this table

ID_A  ID_B
----------
  1    2
 ...  ...

In that example, I have the record (1,2). For me, (1,2) = (2,1). So i don't want to allow my database to store both values. I know I can accomplish it using, triggers or checks and functions. But i was wondering if there is any instruccion like

CREATE UNIQUE CONSTRAINT AS A SET_CONSTRAINT

Upvotes: 3

Views: 54

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93694

You can do this using Instead of Insert trigger.

Demo

Table Schema

CREATE TABLE te(ID_A INT,ID_B INT)

INSERT te VALUES ( 1,2)

Trigger

Go
CREATE TRIGGER trg_name
ON te
instead OF INSERT
AS
  BEGIN
      IF EXISTS (SELECT 1
                 FROM   inserted a
                 WHERE  EXISTS (SELECT 1
                                FROM   te b
                                WHERE  ( ( a.id_a = b.id_b
                                           AND a.id_b = b.id_a )
                                          OR ( a.id_a = b.id_a
                                               AND a.id_b = b.id_b ) )))
        BEGIN
            PRINT 'duplciate record'
            ROLLBACK
        END
      ELSE
        INSERT INTO te
        SELECT Id_a,id_b
        FROM   inserted
  END

SELECT * FROM   te

Insert Script

INSERT INTO te VALUES (2,1) -- Duplicate

INSERT INTO te VALUES (1,2) --Duplicate

INSERT INTO te VALUES (3,2) --Will work

Upvotes: 1

usr
usr

Reputation: 171178

You could write a view like that:

select 1 as Dummy
from T t1
join T t2 on t1.ID1 = t2.ID2 AND t1.ID2 = t2.ID1 --join to corresponding row
cross join TwoRows

And create a unique index on Dummy. TwoRows is a table that contains two rows with arbitrary contents. It is supposed to make the unique index fail if there ever is a row in it. Any row in this view indicates a uniqueness violation.

Upvotes: 2

Related Questions