Rust Diesel is not recognizing primary key

Question

This is my up.sql file:

    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(255),
    `last_name` VARCHAR(255),
    `user_name` VARCHAR(255) NOT NULL UNIQUE,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
);

When I run diesel migration I receive the following error:

Diesel only supports tables with primary keys. Table users has no primary key

Not sure what I'm doing wrong, any help would be greatly appreciated!

Answer 1

I ended up manually creating the table in mysql directly, however when I tried to generate the schema I once again received the "no primary key" error message from the diesel cli. So I began playing around with my sql to see if I could make it work, and eventually it did using the following:

CREATE TABLE users (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `first_name` VARCHAR(255),
    `last_name` VARCHAR(255),
    `user_name` VARCHAR(255) NOT NULL UNIQUE,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

It seems that in order for diesel to recognize a column as having a PRIMARY KEY, the primary key attribute must be the last one specified.

I'm not sure if this is a bug, a known quirk, or valid syntax. In any case it was fairly annoying to track this down. I will do some more testing and accept this answer if it seems to be a robust solution.

(edit: I also tested this with a similar SQL query where AUTO_INCREMENT came after PRIMARY KEY, and it did not work)