SQLiteStudio: How to create unique row (not column)?

Question

I want to create a table that only allows unique rows with SqliteStudio:

This should work:

|--ID--|--Column01--|--Column01--|
| 1     "brun/brune"   "brown"   |
| 2     "yellow"       "brown"   |

This shouldn´t work:

|--ID--|--Column01--|--Column01--|
|   1    "brun/brune"   "brown"  |
|   2    "brun/brune"   "brown"  |

Hope you can help me C: ~Henri

score 3 · Answer 1 · answered Sep 18 '17 at 16:03

3

You can add a UNIQUE CONSTRAINT to the table:

Create Table YourTable
(
    Id       INTEGER PRIMARY KEY AUTOINCREMENT,
    Column01 VARCHAR,
    Column02 VARCHAR,
    CONSTRAINT col01_col02_unique UNIQUE (Column01, Column02)
)

answered Sep 18 '17 at 16:03

Siyual

16,415
8
44
58

Thank, you C: got it :D – MeineHTMLCodes Sep 18 '17 at 16:07
As there is an ID column already a Unique Constraint is better and could be more "domain driven" – Himanshu Sep 19 '17 at 02:37

score 3 · Accepted Answer · answered Sep 19 '17 at 07:38

3

Since the question was asked in context of SQLiteStudio - you can create composite contraints in the table window. While you can add columns in upper part of the table window, the lower part of window is for managing composite constraints:

answered Sep 19 '17 at 07:38

Googie

5,742
2
19
31

Himanshu · Answer 3 · 2017-09-18T22:03:46.700

2

create a multivalued primary key like this

CREATE TABLE something (
  column01, 
  column02, 
  PRIMARY KEY (column01, column02)
);

Both single column and composite (multiple column) primary keys are supported.

See https://www.sqlite.org/lang_createtable.html

edited Sep 18 '17 at 22:03

answered Sep 18 '17 at 16:03

Himanshu

2,384
2
24
42

I suppose it's worth noting that there's a limit on the number of indexes per column. SQLite is rather liberal about this, setting by default at 2000 (https://sqlite.org/limits.html). Also note that the more columns per index, the slower write performance. However, as long as you aren't being too crazy about it, it's not typically noticeable. – jleach Sep 18 '17 at 16:21

score 1 · Answer 4 · answered Sep 18 '17 at 16:17

In the example below There is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).

CREATE TABLE mytable (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_name PRIMARY KEY (ID,LastName)
);

SQLiteStudio: How to create unique row (not column)?

4 Answers4