Unique combination of values

sqliteunique-constraint

I want to create a database for my program. Most likely I will use SQLite, although others are an option.

I will have columns a, b, c, d. a is unique and will be my primary key. b, c, d are allowed to contain duplicate value.

However, I don't want combinations of b, c, d to repeat. For instance, sample valid db:

a   b   c   d
0   18  n   kiwi
1   12  v   kiwi
2   15  t   orange
3   12  j   kiwi
4   12  t   banana
5   31  f   apple
6   10  h   kiwi
7   15  y   orange
8   20  b   apple
9   1   m   grapefruit
10  7   s   apple
11  4   h   banana
12  31  t   egg

Sample invalid db (rows 2 and 7):

a   b   c   d
0   18  n   kiwi
1   12  v   kiwi
2   15  y   orange
3   12  j   kiwi
4   12  t   banana
5   31  f   apple
6   10  h   kiwi
7   15  y   orange
8   20  b   apple
9   1   m   grapefruit
10  7   s   apple
11  4   h   banana
12  31  t   egg

Is there standard database rule or concept that applies to this?

I can of course simply do validation in my code, by manually querying the db. I believe I could also construct a fifth bcd column, which concatenates the other three, and is required to be unique. But both of these seem hacky. Is it possible to somehow "declare the columns unique in combination only" in a more direct and obvious way?

For instance, I could declare (b, c, d) as my composite primary key (not sure if SQLite supports this but I think SQL does). That should accomplish my goal. However, I don't want these columns to actually be my PK.

Best Answer

CREATE UNIQUE INDEX your_unique_index ON your_table(b, c, d);

Create an index instead. It has the same as the unique constraint.

Related Solutions

Best Type for Composite Key with Varying Value Types in SQL Server

I wouldn't even consider forcing data of different types into one field.

Another option:

synthetic post_id and
child tables for each 'type' of 'native_post_id'

There are various ways to go about enforcing the subset relationship between these child tables and the parent if necessary

Mysql – Unique combination key MySQL

Here is before trigger you can use to guarantee that id1 and id2 are inserted in min/max order

DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;

Here is some Sample Data:

DROP DATABASE IF EXISTS ryanzec;
CREATE DATABASE ryanzec;
USE ryanzec
CREATE TABLE mytable
(
    id1 INT NOT NULL,
    id2 INT NOT NULL,
    mapTypeId INT NOT NULL,
    primary key (id1,id2)
);
DELIMITER $$
CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
FOR EACH ROW
BEGIN
    DECLARE sum INT;
    SET sum = NEW.id1 + NEW.id2;
    SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    SET NEW.id2 = sum - NEW.id1;
END $$
DELIMITER ;
INSERT INTO mytable VALUES (1,2,3);
INSERT INTO mytable VALUES (2,1,3);
INSERT INTO mytable VALUES (5,4,6);
INSERT INTO mytable VALUES (4,5,6);
SELECT * FROM mytable;

Here it is the Sample Data Loaded

mysql> DROP DATABASE IF EXISTS ryanzec;
Query OK, 1 row affected (0.03 sec)

mysql> CREATE DATABASE ryanzec;
Query OK, 1 row affected (0.00 sec)

mysql> USE ryanzec
Database changed
mysql> CREATE TABLE mytable
    -> (
    ->     id1 INT NOT NULL,
    ->     id2 INT NOT NULL,
    ->     mapTypeId INT NOT NULL,
    ->     primary key (id1,id2)
    -> );
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER $$
mysql> CREATE TRIGGER mytable_bi BEFORE INSERT ON mytable
    -> FOR EACH ROW
    -> BEGIN
    ->     DECLARE sum INT;
    ->     SET sum = NEW.id1 + NEW.id2;
    ->     SET NEW.id1 = (NEW.id1 + NEW.id2 - ABS(NEW.id1 - NEW.id2)) / 2;
    ->     SET NEW.id2 = sum - NEW.id1;
    -> END $$
Query OK, 0 rows affected (0.10 sec)

mysql> DELIMITER ;

Watch What Happens When I Insert the Rows:

mysql> INSERT INTO mytable VALUES (1,2,3);
Query OK, 1 row affected (0.06 sec)

mysql> INSERT INTO mytable VALUES (2,1,3);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'PRIMARY'
mysql> INSERT INTO mytable VALUES (5,4,6);
Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO mytable VALUES (4,5,6);
ERROR 1062 (23000): Duplicate entry '4-5' for key 'PRIMARY'
mysql> SELECT * FROM mytable;
+-----+-----+-----------+
| id1 | id2 | mapTypeId |
+-----+-----+-----------+
|   1 |   2 |         3 |
|   4 |   5 |         6 |
+-----+-----+-----------+
2 rows in set (0.00 sec)

mysql>

Now, no matter what order id1 and id2 are entered, id1 will always be less than or equal to id2

This may not be contextually what you want because id1 and id2 are unique identifiers.

Related Question