Laravel and clustered index

posted 10 years ago

Hey,

I am developing an application and would now like to move it to my staging server. For the database I'm using AzureSQL.

Everything works fine, but when using migrations I get an error saying:

 SQLSTATE[HY000]: General error: 40054 General SQL Server error: Check messages from the SQL Server [40054] (severity 16)

The error means: "Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again."

Now how can I make my schema using a clustered index with Laravel?

Here's my public up function:

		Schema::create('appkeys', function(Blueprint $table) {
			$table->increments('id');
			$table->string('key', 255);
			$table->integer('client_id');
			$table->integer('user_id');
			$table->string('imei', 255);
			$table->string('meta', 255);
			$table->timestamps();
		});

Any answers are welcomed, I'm stuck at this point. Thank you guys.

Last updated 2 years ago.

nesl247

replied 10 years ago

I'm pretty sure you are going to have to create that one specific table using DB instead of Schema. You should add a ticket to http://github.com/laravel/framework about supporting Azure SQL and the need for the primary key to have a clustered index according to http://blogs.msdn.com/b/sqlazure/archive/2010/04/29/10004618.aspx

The Grammar file for Sql (MSSQL), does not specify the primary key to be clustered, and there is no way to set it as such, thus Schema won't work.

Last updated 2 years ago.

pknecht

replied 10 years ago

Thanks. You mean using DB::statement? Argh the problem occurs with every migration I want to do (above is just one example from many). I had planned to use migrations extensively in the future with another developer joining the project. Any other idea?

Frankly I've heard of clustered index for the first time ;)

Last updated 2 years ago.

pknecht

replied 10 years ago

Solution

OK, problem solved.

I admit I'm a newbie. But for anyone else stumbling across this question here's the simple solution

1.

add ->index() to the id

        Schema::create('appkeys', function(Blueprint $table) {
            $table->increments('id')->index();

2.

Edit the migrations table and set a column as an index. Argh, didn't thought about that one before ;-)

Update:

General

In Laravel you don't necessarily need to specify the id column, it usually will be added automatically. But Laravel will not specify the id column as "index", hence when working with Azure SQL you must add the id column in your migration schema.

Last updated 2 years ago.

1.

2.

General

Moderators