SQL Unique Constraint on a 2 Columns Combination

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

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 to create a unique key with two columns in SQL-Server

The UNIQUE constraint designates a column or combination of columns as a unique key. To satisfy a UNIQUE constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls.

In your case, a student may have marks for several subjects, but the same StudentNumber, SubjectNumber combination may not repeat.

CREATE TABLE StudentDetails(StudentNumber INT NOT NULL, 
                            IndexNumber INT NOT NULL,
                            FirstName VARCHAR(255) NOT NULL,
                            PRIMARY KEY (StudentNumber)
                           )

CREATE TABLE SubjectDetails(SubjectNumber INT NOT NULL
               , SubjectCode INT NOT NULL,
              PRIMARY KEY (SubjectNumber))                           


CREATE TABLE Marks (MarkCode  int NOT NULL, 
                    StudentNumber  int  NOT NULL,
                    SubjectNumber int NOT NULL, 
                    Mark int NULL,

                    PRIMARY KEY (MarkCode),
                    CONSTRAINT unicity UNIQUE (StudentNumber,SubjectNumber)
                    )

See : When to use unique composite keys?

pros and cons of using multi column primary keys

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.

Unique combination of 2 columns, order independent

Starting in MySQL 8.0.13, you can create an index on an expression. That allows you to do:

create unique index unq_friendships_requestor_acceptor on
     friendships( (least(requestor, acceptor)) , (greatest(requestor, acceptor)) );

You should also declare these two columns as NOT NULL.

For older versions. GMB's answer is appropriate.

How can I create a SQL unique constraint based on 2 columns?

You can try this:

CREATE UNIQUE CLUSTERED INDEX index_name ON TABLE (col1,col2)

or

CREATE UNIQUE NONCLUSTERED INDEX index_name ON TABLE (col1,col2)

or

ALTER TABLE [dbo].[TABLE] ADD CONSTRAINT
    UNIQUE_Table UNIQUE CLUSTERED
    (
       col1,
       col2
    ) ON [PRIMARY]

unique constraints for multiple columns in mysql

You need to add a Composite UNIQUE constraint on COMPANY_ID and BARCODE, to your table. This basically means that it will not allow rows having duplicate combination of values, for the said two fields. It can still allow duplicate values individually for either of them.

Eg: (1, 'abc') combination will not be able to exist in more than one row. However, (1, 'abc'), (1, 'def') can exist. So it allows duplicate values individually (Company_id in this example)

Do the following (change table and column name(s) accordingly):

ALTER TABLE your_table_name 
ADD CONSTRAINT unique_barcode_company UNIQUE (COMPANY_ID, BARCODE)

Unique constraint on two columns for any combination in SQL Server?

The constraint may be created either when the table is created or by altering an existing table.

Create a new table

create table SomeTable(
  Col_1     varchar(20),
  Col_2     varchar(20),
  constraint unq_SomeTable_c1_c2 unique(Col_1, Col_2));
go

Alter an existing table

alter table SomeTable
add
  constraint unq_SomeTable_c1_c2 unique(Col_1, Col_2);

If duplicate insert is attempted the error message will be as follows

Msg 2627, Level 14, State 1, Line 7502
Violation of UNIQUE KEY constraint 'unq_SomeTable_c1_c2'. Cannot insert duplicate key in object 'dbo.SomeTable'. The duplicate key value is (ABC, DEF).
The statement has been terminated.

[EDIT] to ensure the order of the columns does not matter I created a TRIGGER which runs after INSERT or UPDATE. The error message could be changed to whatever you wish.

create trigger trg_SomeTable_unq_c2c1_ins_upd on SomeTable
after insert, update
as
if exists(select 1 
            from SomeTable st join inserted i on st.Col_2=i.Col_1
                                            and st.Col_1=i.Col_2)
    rollback transaction;
go

Test Case

insert SomeTable(Col_1, Col_2) values
('ABC', 'DEF'),
('QER', 'ZXC'),
('ASD', 'VBN');

insert SomeTable(Col_1, Col_2) values
('DEF', 'ABC');

Output

    (3 row(s) affected)
    
Msg 3609, Level 16, State 1, Line 7514
The transaction ended in the trigger. The batch has been aborted.

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.

---

Related Topics