CODEWITHSUNDEEP
sqlsql-server
Real Programmer
Real Programmer

Reputation: 337

how to make two columns are unique in SQL Server?

Good Day Friends,

I want to know that how to make two columns are unique in SQL server in the special manner as I explained below.

The Way I want : 

------------------------- 
  User    | Friend      |  
-------------------------
  Stevan  | Johns     } 
  William | David     }This is how I want two columns to be unique.

The Way I don't Want : 

-------------------------
  User    | Friend    | 
-------------------------
 Steven   | Johns     }   
 Johns    | Steven    }This must not allow.

Steven is a friend of Johns So They are friends so I don't want Johns to add a new row saying Johns is a friend of Steven.

Steven add new row like this : 

-------------------------
  User    | Friend    | 
-------------------------
 Steven   | Johns     }

I Don't want john to add a row again Like this 

-------------------------
  User    | Friend    | 
-------------------------
 Steven   | Johns     }   
 Johns    | Steven    }

I hop my question is clear, IF someone know a good answer for this , please help me. Thanks in advance for any answer

Upvotes: 3

Views: 588

Answers (2)

wazaminator
wazaminator

Reputation: 245

i think,because a primary key won't work, you will have to use a trigger to check if the line allready exist.

Upvotes: -1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

The straightforward way to do this (all else being equal) is to insist that the value in the first column always sort earlier than the value in the second column:

CREATE TABLE Friends (
    Party1 varchar(20) not null,
    Party2 varchar(20) not null,
    constraint CK_Friend_Parties CHECK (Party1 < Party2),
    constraint CK_Friends_Unique UNIQUE (Party1,Party2)
)

If you're unable to accommodate this change (which is odd, because it then suggests that the relationship isn't symmetric), you can enforce it via an indexed view:

create table dbo.T1 (
    Party1 varchar(20) not null,
    Party2 varchar(20) not null
)
go
create view dbo.V1
with schemabinding
as
    select
        CASE WHEN Party1 < Party2 THEN Party1 else Party2 END as NormParty1,
        CASE WHEN Party1 < Party2 THEN Party2 else Party1 END as NormParty2
    from
        dbo.T1
go
create unique clustered index IX_V1 on dbo.V1 (NormParty1,NormParty2)
go
insert into dbo.T1 (Party1,Party2) values ('Steven','John')
go
insert into dbo.T1 (Party1,Party2) values ('John','Steven')

The final insert above generates an error. Note that, for most intents, you ignore the view V1 - it only exists to enforce this constraint (when I use such tables, I usually prefix their name with DRI_ to make it clear that it wasn't really created to be queried.

Upvotes: 4

Related Questions