Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

nekooee's avatar

How to create a table with one unique column at the same time with one query?

How to create a table with one unique column with the same sql query? That is, with one query. I wrote this code:

Schema::disableForeignKeyConstraints();
Schema::table('orders', function (Blueprint $table): void {
	$table->id();
	$table->unsignedBigInteger('order_number')->unique();
});
Schema::enableForeignKeyConstraints();

But this is executed in the two queries. First, the table and columns are created, and then uniqe is added to it:

1. create table `orders` (`id` bigint unsigned not null auto_increment primary key, `order_number` bigint unsigned not null) default character set utf8mb4 collate 'utf8mb4_unicode_ci'
2. alter table `orders` add unique `unique_order_number`(`order_number`)

Since this table is related to previously created tables, it causes the following error: 1822 Failed to add the foreign key constraint. Missing index for constraint 'invoices_order_number_foreign' in the referenced table 'orders'

I didn't find any solution except to write the sql manually:

 Schema::disableForeignKeyConstraints();
        DB::statement('
            create table orders (
                id bigint unsigned auto_increment not null primary key,
                order_number bigint unsigned not null,
                unique unique_order_number (order_number)
            ) default character set utf8mb4 collate utf8mb4_unicode_ci
        ');
Schema::enableForeignKeyConstraints();

In this query, because unique is added at the same time, I don't get any error and everything is correct. But I don't like to query manually. Is there no other solution for this problem??

0 likes
3 replies
jlrdw's avatar

Where you have:

$table->unsignedBigInteger('order_number')->unique();

Is that a parent or child table? If child, order_number won't be unique.

nekooee's avatar

@jlrdw this is parent. Anyway, I still haven't found a solution other than manual sql query.

Tray2's avatar

@nekooee Why worry about the number of queries in a migration, it's not like you need it to be super fast?

Please or to participate in this conversation.