1

I have a table named Followers which contains one PK column and two FK columns which will store integers of the table named User. However i dont want to have replicated values in columns - the combination of two FKs needs to always be different. How do i do that?

Milos Novakovic
  • 11
  • 1
  • 2
  • 2
    Possible duplicate of [Add unique constraint to combination of two columns](https://stackoverflow.com/questions/15800250/add-unique-constraint-to-combination-of-two-columns) – Michał Turczyn May 07 '18 at 09:54
  • Without DDL and a clear description of your requirement, only guessing is possible. Do you consider the set of values (1, 2) different from (2, 1) - ignoring your identity primary key column? – SMor May 07 '18 at 12:45

2 Answers2

2

Create a single unique key for both the columns

Aswani Madhavan
  • 816
  • 6
  • 19
2

A unique index or constraint will solve your problem. Unique constraints are implemented as unique indexes behind the scenes, so your selection of either solution is trivial.

To demonstrate, let's assume the following contrived scenario.

create table User (
  Id int identity primary key,
  name varchar(255) not null
);
go

create table Follower (
  Id int identity primary key,
  UserId int foreign key references User(Id),
  FollowerId int foreign key references User(Id)
);
go

To ensure the uniqueness of UserId & FollowerId add the following unique index.

create unique index ux_follower_userid_followerid
  on Follower
  (
    UsrId
    ,FollowerId
  );
go

Note that it's generally advisable to also include non-clustered indexes on foreign key columns to facilitates joins.

pim
  • 12,019
  • 6
  • 66
  • 69