SQLite - Any difference between table-constraint UNIQUE & column-constraint UNIQUE?

Question

Question about SQLite.

In the CREATE TABLE SQL, we can add UNIQUE constraints in either way: column-constraint or table-constraint. My question is simple. Do they work differently?

The only difference I could find was, in table-constraint, there could be multiple indexed-columns in a single constraint.

Column-constraint:

Table-constraint:

Here is an example:

CREATE TABLE Example (
    _id INTEGER PRIMARY KEY,
    name TEXT UNIQUE ON CONFLICT REPLACE,
    score INTEGER
)

and

CREATE TABLE Example (
    _id INTEGER PRIMARY KEY,
    name TEXT,
    score INTEGER,
    UNIQUE (name) ON CONFLICT REPLACE
)

Are they different?

Harkey

Answer 1

In this case there is no difference.

However, you could create an unique constraint on table, that would span over two different columns. Like this:

CREATE TABLE Example (
    _id INTEGER PRIMARY KEY,
    name TEXT,
    index INTEGER,
    score INTEGER,
    UNIQUE (name, index) ON CONFLICT REPLACE
)

Consult this post for further details: SQLite table constraint - unique on multiple columns

Stilton