Sqlite Unique Key with a Combination of Two Columns

SQLite table constraint - unique on multiple columns

Put the UNIQUE declaration within the column definition section; working example:

CREATE TABLE a (
    i INT,
    j INT,
    UNIQUE(i, j) ON CONFLICT REPLACE
);

SQLite Unique Key with a combination of two columns

Add to your ddl

create table ... ( ...
...,
unique(slot, player));

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.

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.

Sqlite create unique pair of columns

Add a separate constraint for both columns:

CREATE TABLE MyTable(
    user_id INTEGER,
    image_id INTEGER,
    [...],
    UNIQUE(user_id, image_id)
)

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

How can I enforce uniqueness on a combination of two columns in a database table?

You can create a unique index to avoid inserting more than one row with the same couple (name, id_catalog#).

Create the table:

SQL> create table Product(id_product number, name varchar2(10), quantity number, id_catalog# number);

Table created.

Add an index :

SQL> create unique index idx1 on Product (name, id_catalog#);

Index created.

This will work:

SQL> insert into Product (id_product, name, quantity, id_catalog#) values (1, 'chocolat', 5, 1);

1 row created.

This will not work:

SQL> insert into Product (id_product, name, quantity, id_catalog#) values (2, 'chocolat', 7, 1);
insert into Product (id_product, name, quantity, id_catalog#) values (2, 'chocolat', 7, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (ALEK.IDX1) violated

But this will, due to different id_catalog#

SQL> insert into Product (id_product, name, quantity, id_catalog#) values (2, 'chocolat', 7, 2);

1 row created.

SQL>

Sqlite primary key on multiple columns

According to the documentation, it's

CREATE TABLE something (
  column1, 
  column2, 
  column3, 
  PRIMARY KEY (column1, column2)
);

---

Related Topics