Support last insert ID for composite primary key

[namco1992]

Here is the current implementation for CanLastInsertID: https://github.com/volatiletech/sqlboiler/blob/d551047361e2a3580169ce51d6445db17ae0d073/drivers/table.go#L44-L48

However, according to the MySQL doc, it's possible to retrieve the last insert ID as long as there is an AUTO_INCREMENT column.

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.

However, I'm not sure if it's the same for other DBs that are supported by sqlboiler. I would like to discuss the possible solutions here and see if we can loosen the restriction since they are not accurate. Thanks!

[aarondl]

Well, the first step would be to look up how the rest of the database engines handle it. Afaik only mysql/sqlite3 use the LAST_INSERT_ID because Postgresql and MSSQL (need to confirm MSSQL) use a returning statement.

[namco1992]

Hello @aarondl, according to the doc of pq and mssql, the two libs that implement the database/sql interface don't support the lastInsertId.

For sqlite3:

With one exception noted below, if a rowid table has a primary key that consists of a single column and the declared type of that column is "INTEGER" in any mixture of upper and lower case, then the column becomes an alias for the rowid. Such a column is usually referred to as an "integer primary key".

In short, neither MySQL nor sqlite3 requires a primary key to support the LAST_INSERT_ID. Although it's common (and most of the time recommended) to have an auto inc integer column as PK, it's actually not the necessary condition for supporting LAST_INSERT_ID.

[aarondl]

The real problem with this is struct value binding. We use last insert id to retrieve the mysql row back from the database and fill the struct with values.

If you have a composite key (a, b), and a is a last insert id and b is some other default value. You can no longer get the row back to fill structs because last insert id will only give you a half of that. There's many scenarios in which this fails which is why the restriction is there. If we can come up with the full list of scenarios and try to support ones that will work in all cases I'm all for hearing them, but it's not 100% trivial.