Laravel migration fails on managed MySQL because a table has no primary key
SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set.Short answer
The server has sql_require_primary_key switched on, so every CREATE TABLE or ALTER TABLE must leave the table with a primary key. Add one in the migration, usually with $table->id(). Turning the setting off is possible on DigitalOcean only through its API, and it is risky on a cluster with more than one node.
Fix it fast
1Find out which cause you have
Ask the server whether the rule is on for your connection. If it returns 1, every table your migrations create needs a primary key.
SHOW VARIABLES LIKE 'sql_require_primary_key';2Match what you see, then jump to its fix
- 1
The failing migration calls Schema::create() without $table->id() or a primary() call.A migration creates a table (often a pivot table) with no primary keyFix - 2
The error appears on an ALTER TABLE or while importing a dump, not on your own CREATE TABLE.A package migration or an ALTER TABLE drops or never adds the keyFix - 3
You cannot change the schema, for example a vendor package you do not own.You really need the rule off (single-node, throwaway or legacy data)Fix
- 1
What does the error look like?
SQLSTATE[HY000]: General error: 3750 Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set.Add a primary key to the table or unset this variable to avoid this message.
Cause 1 of 3
A migration creates a table (often a pivot table) with no primary key
1Check
Open the migration named in the error and look for a table that only has foreign key columns or plain columns.
php artisan migrate:status2Fix
Give the table a primary key. For a normal table use an auto-increment id. For a pivot table, make the two foreign keys a composite primary key.
Schema::create('posts', function (Blueprint $table) {
$table->id();
$table->string('title');
});
Schema::create('post_tag', function (Blueprint $table) {
$table->foreignId('post_id')->constrained();
$table->foreignId('tag_id')->constrained();
$table->primary(['post_id', 'tag_id']);
});3Verify
Run the migration again; it should finish without error 3750.
php artisan migrateConfirmed by laravel.com
Cause 2 of 3
A package migration or an ALTER TABLE drops or never adds the key
1Check
MySQL also rejects dropping a primary key, and importing a table that has none, while the variable is on. Dropping and re-adding the key in the same ALTER TABLE is allowed.
SHOW CREATE TABLE my_table;2Fix
Add the key with ALTER TABLE, then retry. In Laravel a column that must be the key can be marked primary() when you change it.
ALTER TABLE my_table ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;3Verify
SHOW CREATE TABLE now lists PRIMARY KEY.
SHOW CREATE TABLE my_table;Confirmed by dev.mysql.com
Cause 3 of 3
You really need the rule off (single-node, throwaway or legacy data)
1Check
On DigitalOcean the setting is True by default and the control panel cannot change it. It is changed with a PATCH request to the cluster config endpoint. DigitalOcean advises keeping it on for clusters with more than one node, because turning it off may severely impact replication.
SHOW VARIABLES LIKE 'sql_require_primary_key';2Fix
Prefer adding keys. If you must, send the config request with the value false for a single-node cluster.
curl -X PATCH \
-H "Content-Type: application/json" \
-H "Authorization: Bearer $DIGITALOCEAN_TOKEN" \
-d '{"config": {"sql_require_primary_key": false}}' \
"https://api.digitalocean.com/v2/databases/<cluster-uuid>/config"3Verify
Reconnect and read the variable again.
SHOW VARIABLES LIKE 'sql_require_primary_key';Confirmed by docs.digitalocean.com
How do I stop it coming back?
- Give every table you create an id() or an explicit primary() key, including pivot tables.
- Run migrations against a staging database with the same managed settings before production.
- Keep the primary key rule on for any cluster with standby or read-only nodes.
Sources
How was this page made?
I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.
Which stack is behind this page?
Projects of mine built on the same technologies:
- Spotless LaundryAn on-demand laundry app: Flutter on a Laravel back end, with Firebase for live updates.
- EZ EstimaterConstruction quoting and job tracking for contractors, on a Laravel and Firebase stack.
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
Related fixes
Databases
Laravel bulk insert fails with 'too many placeholders'
SQLSTATE[HY000]: General error: 1390 Prepared statement contains too many placeholders
Laravel sends one placeholder per value, so one insert of many rows times many columns, or a whereIn with a huge list, creates a statement MySQL refuses.
3 causes, each with a fixDatabases
Laravel query fails: conversion from one collation into another is impossible
SQLSTATE[HY000]: General error: 3988 Conversion from collation utf8_unicode_ci into utf8mb4_0900_ai_ci impossible for parameter
MySQL is comparing or binding a value whose character set cannot be converted into the one on the other side.
3 causes, each with a fixDatabases
PHP cannot connect to MySQL: the connection attempt failed
Error 2003
Error 2003 means the TCP connection to MySQL never opened.
4 causes, each with a fixDatabases
The client cannot reach the MySQL or MariaDB server through its Unix socket file
ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (2) (MariaDB client wording)
Error 2002 means the client tried the Unix socket file named in the message and found nothing it could talk to.
4 causes, each with a fixDatabases
MySQL or MariaDB: Column count of mysql.proc is wrong, table probably corrupted
Error 1558: Column count of mysql.proc is wrong
The mysql.proc system table has a different number of columns than the running server expects.
3 causes, each with a fixDatabases
Saving an empty date from PHP into a MySQL DATETIME column fails
The column holds 0000-00-00 00:00:00 instead of NULL
An empty string is not NULL.
4 causes, each with a fix