Add Unique Constraint to Combination of Two Columns

Add unique constraint to combination of two columns

Once you have removed your duplicate(s):

ALTER TABLE dbo.yourtablename
  ADD CONSTRAINT uq_yourtablename UNIQUE(column1, column2);

or

CREATE UNIQUE INDEX uq_yourtablename
  ON dbo.yourtablename(column1, column2);

Of course, it can often be better to check for this violation first, before just letting SQL Server try to insert the row and returning an exception (exceptions are expensive).

• Performance impact of different error handling techniques

• Checking for potential constraint violations before entering TRY/CATCH

If you want to prevent exceptions from bubbling up to the application, without making changes to the application, you can use an INSTEAD OF trigger:

CREATE TRIGGER dbo.BlockDuplicatesYourTable
 ON dbo.YourTable
 INSTEAD OF INSERT
AS
BEGIN
  SET NOCOUNT ON;

  IF NOT EXISTS (SELECT 1 FROM inserted AS i 
    INNER JOIN dbo.YourTable AS t
    ON i.column1 = t.column1
    AND i.column2 = t.column2
  )
  BEGIN
    INSERT dbo.YourTable(column1, column2, ...)
      SELECT column1, column2, ... FROM inserted;
  END
  ELSE
  BEGIN
    PRINT 'Did nothing.';
  END
END
GO

But if you don't tell the user they didn't perform the insert, they're going to wonder why the data isn't there and no exception was reported.

---

EDIT here is an example that does exactly what you're asking for, even using the same names as your question, and proves it. You should try it out before assuming the above ideas only treat one column or the other as opposed to the combination...

USE tempdb;
GO

CREATE TABLE dbo.Person
(
  ID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(32),
  Active BIT,
  PersonNumber INT
);
GO

ALTER TABLE dbo.Person 
  ADD CONSTRAINT uq_Person UNIQUE(PersonNumber, Active);
GO

-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);
GO

-- succeeds:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 0, 22);
GO

-- fails:
INSERT dbo.Person(Name, Active, PersonNumber)
  VALUES(N'foo', 1, 22);
GO

Data in the table after all of this:

ID   Name   Active PersonNumber
---- ------ ------ ------------
1    foo    1      22
2    foo    0      22

Error message on the last insert:

Msg 2627, Level 14, State 1, Line 3
Violation of UNIQUE KEY constraint 'uq_Person'. Cannot insert duplicate key in object 'dbo.Person'.
The statement has been terminated.

Also I blogged more recently about a solution to applying a unique constraint to two columns in either order:

• Enforce a Unique Constraint Where Order Does Not Matter

Combination of two columns unique constraint

 CREATE UNIQUE INDEX idx_twocols ON t1t2(t1_id, t2_id)

You will probably need to add NOT NULL to the declarations for each of the two columns.

Alternatively, you could choose to forego the primary key column (if all you're using it for is uniqueness) and create the primary key on the combination of t1_id and t2_id:

CREATE TABLE t1t2(
t1_id integer NOT NULL,
t2_id integer NOT NULL,
PRIMARY KEY (t1_id, t2_id),
foreign key(t1_id) references t1(id),
foreign key(t2_id) references t2(id));

The PRIMARY KEY is a special case of a UNIQUE index. Using the composite PRIMARY KEY saves you one column and one index, but requires your application to know both t1_id and t2_id to retrieve a single row from the table.

Can I make a unique constraint in Oracle on a combination of 2 columns from 2 different tables?

Yes, you can create a UNIQUE constraint on two columns. For example:

create table table_a (
  a number(6) primary key not null
);

create table table_b (
  b number(6) not null,
  c number(6) not null,
  constraint uq1 unique (b, c),
  constraint fk1 foreign key (c) references table_a (a)
);

Then, if you try to insert it will fail for duplicates. For example:

insert into table_a (a) values (1);
insert into table_a (a) values (2);
insert into table_b (b, c) values (10, 1);
insert into table_b (b, c) values (10, 1); -- fails!
insert into table_b (b, c) values (10, 2);

See running example at db<>fiddle.

How do I specify unique constraint for multiple columns in MySQL?

To add a unique constraint, you need to use two components:

ALTER TABLE - to change the table schema and,

ADD UNIQUE - to add the unique constraint.

You then can define your new unique key with the format 'name'('column1', 'column2'...)

So for your particular issue, you could use this command:

ALTER TABLE `votes` ADD UNIQUE `unique_index`(`user`, `email`, `address`);

Unique constraint on any combination of two columns

Yes, it is possible(for example using generated columns):

CREATE TABLE tab(A INT NOT NULL, B INT NOT NULL);

ALTER TABLE tab ADD c1 AS (LEAST(A,B));
ALTER TABLE tab ADD c2 AS (GREATEST(A,B));
CREATE UNIQUE INDEX UQ_tab ON tab(c1,c2);

You could hide these columns if needed(Oracle 12c):

ALTER TABLE tab MODIFY c1 INVISIBLE;
ALTER TABLE tab MODIFY c2 INVISIBLE;

DBFiddle Demo

EDIT:

Even simpler approach:

CREATE UNIQUE INDEX UQ_tab ON tab(least(A,B), greatest(A,B));

DBFiddle Demo

Unique constraint on two columns irrespective of order

Create a unique index:

CREATE UNIQUE INDEX ON atable
   (LEAST(name, alternate), GREATEST(name, alternate));

UNIQUE Constraint On Combination of Two Column Values

For same relations - use a unique constraint on <sender, receiver>.

The inverse relation <receiver, sender> however will be possible, cause it's different ids for the unique key constraint.

To handle this (using a uniqe key constraint), you have to add another column, let's call it friendship - There, you'll add a unique key constraint, and insert the users ids, concatenated, BUT ORDERED:

I.e. If a user 3 sends a friend request to 10, you'll insert 3-10 to that column. If the invitation goes from 10 to 3, you'll add 3-10 as well.

This way, you can keep track of WHO initiated the friendship (sender_column=3, receiver-column=10) but also ensure that there is no backwards invite (friendship=3-10 already exists)

So that's something like

INSERT INTO friendships(sender, receiver, friendship) VALUES(3,10,"3-10");
or vice versa:
INSERT INTO friendships(sender, receiver, friendship) VALUES(10,3,"3-10");

One of both constraints will avoid the insertion if the friendship has been already requested. (Actually the second constraint would be sufficent for any case, first 2 columns would only allow to determine the active and passive part of the friendship.)

Unique constraint on combination of two columns?

You can do this using an index on expressions:

create unique index unq_test_a_b on (test(least(a, b), greatest(a, b));

I don't think the unique constraint allows expressions (and don't have a convenient Postgres to test on right now), but this is essentially the same thing.

Unique constraint for 2 columns that works both ways

You can create a unique index that always indexes the same order of values:

create unique index 
   on friends (least(requestor, requestee), greatest(requestor, requestee));

---

Related Topics