Database create(table:) - Why should multiple columns in primary key be consecutive?

[changyjacky]

Sqlite allow this CREATE TABLE statement:

CREATE TABLE team_member
(  playerid INTEGER,
   note TEXT,
   teamid INTEGER,
   PRIMARY KEY (playerid, teamid)
)

But GRDB Database create table function must be:

try db.create(table: "team_member") { t in
    t.primaryKey {
        t.column("playerid", .integer)
        t.column("teamid", .integer)
    }
    t.column("note", .text)
}

You just CAN'T keep the "note" column between the two primary-key columns.
It sounds not that bad; But when you design the UI, it will be a trouble,
you must change the column order when user take non-consecutive columns as primary key.
It doen't make sense.

Is there any alternative solution?
(Just don't want to turn back to SQL CREATE statement...)

[groue]

Hello @changyjacky,

Your alternative solution is to add an explicit PRIMARY KEY constraint:

try db.create(table: "team_member") { t in
    t.column("playerid", .integer).notNull()
    t.column("note", .text)
    t.column("teamid", .integer).notNull()
    t.primaryKey(["playerId", "teamId"])
}

Take care, though, to add a NOT NULL constraint to each primary key column.

I'll update the documentation so that it is clearer.

[groue]

Documentation has been updated in 302fc69

[changyjacky]

Documentation has been updated in 302fc69

Thanks a lot!