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

Answer 1

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:

Answer 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

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

Answer 4

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