SQLite Forum

about "strict" mode
Login
A table can have multiple uniquely identifying columns, we can call those *candidate keys*.
One of the candidate keys is chosen as the *primary key*.

It is just a matter of taste which of the candidate keys is chosen as the primary key, typically it is the key that foreign keys refer to.

The other candidate keys should have a UNIQUE constraint. Without that constraint, you can't call it a key.

If you add an extra INTEGER candidate key for performance reasons, so it  can serve as the key that is referenced by foreign keys, it should become the primary key of the table.

This is called a surrogate key, because it does not represent the attribute of an entity in the real world.
 

In SQLite I would implement that as follows:

Before:
```sql
CREATE TABLE T1 (
  text_pk TEXT PRIMARY KEY NOT NULL
, ...
) WITHOUT ROWID STRICT;
```

After:
```sql
CREATE TABLE T1 (
  surrogate_pk INTEGER PRIMARY KEY NOT NULL
, text_pk TEXT UNIQUE NOT NULL
, ...
) STRICT;
```