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.
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 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));
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 value Constraint with multiple columns across the table, not the combination in Oracle
I would use a check()
constraint to ensure unicity on each row, and a unique index for unicity across rows:
create table mytable (
id int,
phone1 int,
phone2 int,
check (phone1 <> phone2)
);
create unique index myidx on mytable(
greatest(coalesce(phone1, phone2), coalesce(phone2, phone1)),
least(coalesce(phone1, phone2), coalesce(phone2, phone1))
);
The upside of this approach is that it also prevents inserts of tuples like (111, 222)
and (222, 111)
.
Demo on DB Fiddle:
insert into mytable values(1, 111, 111);
ORA-02290: check constraint (FIDDLE_SMBYKTEIHNNVOHKZSCYK.SYS_C0020876) violated
begin
insert into mytable values(1, 111, null);
insert into mytable values(1, 111, null);
end;
/
ORA-00001: unique constraint (FIDDLE_SMBYKTEIHNNVOHKZSCYK.MYIDX) violated
ORA-06512: at line 3
begin
insert into mytable values(1, 111, null);
insert into mytable values(1, null, 111);
end;
/
ORA-00001: unique constraint (FIDDLE_SMBYKTEIHNNVOHKZSCYK.MYIDX) violated
ORA-06512: at line 3
begin
insert into mytable values(1, 111, 222);
insert into mytable values(1, 222, 111);
end;
/
ORA-00001: unique constraint (FIDDLE_SMBYKTEIHNNVOHKZSCYK.MYIDX) violated
ORA-06512: at line 3
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
Related Topics
Postgresql Function Definition in Squirrel: Unterminated Dollar-Quoted String
Can Insert [...] on Conflict Be Used for Foreign Key Violations
Comma Separated Values in One Column - SQL Server
How to Run Multiple SQL Queries
Oracle Client and Networking Components Were Not Found
Split String into Table Given Row Delimiter and Column Delimiter in SQL Server
Exporting SQL Server Table to Multiple Part Files
Merging Intervals in One Pass in SQL
Avoid String Concatenation to Create Queries
MySQL - Creating Rows VS. Columns Performance
How Is This Script Updating Table When Using Left Joins
Unique Constraint on Combination of Two Columns
How to Select a List of 10,000 Unique Ids from Dual in Oracle SQL
SQL Query of Haversine Formula in SQL Server