1

I have one table with contains foreign key "columnA" column and one more column "columnB". I want to prevent adding same values in "columnB" but only for same value in "columnA"...

columnA  columnB 
 1        'a'       
 1        'a' - this is not allowed  
 2        'a' - this is allowed

From my perspective only way to do that is by using trigger, but i suppose that there is a better, more elegant way to make this constraint. Do you know best way to make this logic?

gotqn
  • 42,737
  • 46
  • 157
  • 243
MiroslavStojakovic
  • 167
  • 1
  • 8

3 Answers3

1

A Unique constraint would work.

alter table TableName add constraint UQ_consrtaint unique(columnA, columnB);

That should do it.

Brian Stork
  • 945
  • 2
  • 8
  • 14
0

It looks like you need to create a primary key like this:

DECLARE @DataSource TABLE
(
    [A] TINYINT
   ,[B] CHAR
   ,PRIMARY KEY([A], [B])
);

INSERT INTO @DataSource ([A], [B])
VALUES (1, 'a'); -- ok

INSERT INTO @DataSource ([A], [B])
VALUES (2, 'a'); -- ok

INSERT INTO @DataSource ([A], [B])
VALUES (1, 'a'); -- error

It will give you the following error:

Msg 2627, Level 14, State 1, Line 14 Violation of PRIMARY KEY constraint 'PK__#B1CFBEC__D86D1834E734E52B'. Cannot insert duplicate key in object 'dbo.@DataSource'. The duplicate key value is (1, a).

in the case above.

Or unique constrain on the two columns:

DECLARE @DataSource TABLE
(
    [A] TINYINT
   ,[B] CHAR
   ,UNIQUE ([A], [B])
);
gotqn
  • 42,737
  • 46
  • 157
  • 243
0
ALTER TABLE tablename ADD UNIQUE uniqueconstraintname(columnA, columnB);
Indra Prakash Tiwari
  • 947
  • 1
  • 6
  • 15