How to Enforce Set-Like Uniqueness Between Multiple Columns

How do I enforce set-like uniqueness between multiple columns?

create unique index idx_unique_ab 
    on x (least(a,b), greatest(a,b));

Enforcing mutual uniqueness across multiple columns

You could create an "external" constraint in the form of an indexed view:

CREATE VIEW dbo.OccupiedRooms
WITH SCHEMABINDING
AS
SELECT r.Id
FROM   dbo.Occupants AS o
INNER JOIN dbo.Rooms AS r ON r.Id IN (o.LivingRoomId, o.DiningRoomId)
;
GO

CREATE UNIQUE CLUSTERED INDEX UQ_1 ON dbo.OccupiedRooms (Id);

The view is essentially unpivoting the occupied rooms' IDs, putting them all in one column. The unique index on that column makes sure it does not have duplicates.

Here are demonstrations of how this method works:

failed insert;
successful insert.

UPDATE

As hvd has correctly remarked, the above solution does not catch attempts to insert identical LivingRoomId and DiningRoomId when they are put on the same row. This is because the dbo.Rooms table is matched only once in that case and, therefore, the join produces produces just one row for the pair of references.

One way to fix that is suggested in the same comment: additionally to the indexed view, use a CHECK constraint on the dbo.OccupiedRooms table to prohibit rows with identical room IDs. The suggested LivingRoomId <> DiningRoomId condition, however, will not work for cases where both columns are NULL. To account for that case, the condition could be expanded to this one:

LivingRoomId <> DinindRoomId AND (LivingRoomId IS NOT NULL OR DinindRoomId IS NOT NULL)

Alternatively, you could change the view's SELECT statement to catch all situations. If LivingRoomId and DinindRoomId were NOT NULL columns, you could avoid a join to dbo.Rooms and unpivot the columns using a cross-join to a virtual 2-row table:

SELECT  Id = CASE x.r WHEN 1 THEN o.LivingRoomId ELSE o.DiningRoomId END
FROM    dbo.Occupants AS o
CROSS
JOIN    (SELECT 1 UNION ALL SELECT 2) AS x (r)

However, as those columns allow NULLs, this method would not allow you to insert more than one single-reference row. To make it work in your case, you would need to filter out NULL entries, but only if they come from rows where the other reference is not NULL. I believe adding the following WHERE clause to the above query would suffice:

WHERE o.LivingRoomId IS NULL AND o.DinindRoomId IS NULL
   OR x.r = 1 AND o.LivingRoomId IS NOT NULL
   OR x.r = 2 AND o.DinindRoomId IS NOT NULL

pgsql how to enforce uniqueness for some keys

The only way to do that with a constraint that I can think of is this:

add the uniqueness column to table2 too and fill it with the proper value
define a UNIQUE constraint on table1(attribute_id, uniqueness)

define a foreign key constraint on table2:

ALTER TABLE table2 ADD FOREIGN KEY (attribute_id, uniqueness)
   REFERENCES table1 (attribute_id, uniqueness);

define a partial unique index on table2:

CREATE UNIQUE INDEX ON table2 (attribute_id, value) WHERE uniqueness;

Of course this will lead to data duplication, but the foreign key will guarantee that no inconsistencies can occur.

Enforcing a unique relationship over multiple columns where one column is nullable

CREATE TABLE PERSON 
(
  ID INT NOT NULL
, PERSON_ID INT NOT NULL
, PLAN CHAR(3) NOT NULL
, EMPLOYER_ID INT
, TERMINATION_DATE DATE
);

INSERT INTO PERSON (ID, PERSON_ID, PLAN, EMPLOYER_ID, TERMINATION_DATE)
VALUES 
  (1, 123, 'ABC', 321, DATE('2020-01-01'))
, (2, 123, 'DEF', 321, CAST(NULL AS DATE))
, (3, 123, 'ABC', 321, CAST(NULL AS DATE))
WITH NC;

--- To not allow: ---

INSERT INTO PERSON (ID, PERSON_ID, PLAN, EMPLOYER_ID, TERMINATION_DATE) VALUES 
(4, 123, 'ABC', 321, CAST(NULL AS DATE))
or
(4, 123, 'ABC', 321, DATE('2020-01-01'))

You may:

CREATE UNIQUE INDEX PERSON_U1 ON PERSON 
(PERSON_ID, PLAN, EMPLOYER_ID, TERMINATION_DATE);

--- To not allow: ---

INSERT INTO PERSON (ID, PERSON_ID, PLAN, EMPLOYER_ID, TERMINATION_DATE) VALUES 
(4, 123, 'ABC', 321, DATE('2020-01-01'))

but allow multiple:

(X, 123, 'ABC', 321, CAST(NULL AS DATE))
(Y, 123, 'ABC', 321, CAST(NULL AS DATE))
...

You may:

CREATE UNIQUE WHERE NOT NULL INDEX PERSON_U2 ON PERSON 
(PERSON_ID, PLAN, EMPLOYER_ID, TERMINATION_DATE);

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));

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`);

How to enforce a unique constraint across multiple tables?

I would create a single table person that contains all attributes that are common to both including a type column that identifies teachers and students. Then you can create unique constraints (or indexes) on the phone and email columns.

To store the "type specific" attributes (graduation year, department) you can either have nullable columns in the person table and only put in values depending on the type. If you do not expect to have more "type specific" attributes apart from those two, this is probably the easiest solution

If you expect more "type specific" attributes, additional tables (student and teacher) with containing those can also be used. This is the traditional way of modelling inheritance in a relational database. As Postgres supports table inheritance, you could also create the teacher and student tables to inherit from the person table.

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);

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

In Postgresql, force unique on combination of two columns

CREATE TABLE someTable (
    id serial PRIMARY KEY,
    col1 int NOT NULL,
    col2 int NOT NULL,
    UNIQUE (col1, col2)
)

autoincrement is not postgresql. You want a integer primary key generated always as identity (or serial if you use PG 9 or lower. serial was soft-deprecated in PG 10).

If col1 and col2 make a unique and can't be null then they make a good primary key:

CREATE TABLE someTable (
    col1 int NOT NULL,
    col2 int NOT NULL,
    PRIMARY KEY (col1, col2)
)