FuelPHP Forums

Sariel

Hi all, someone can help me, I wanna set one of the fields of my table as unique. How can I do it in migrations?

Knud

I'm a newbie to FuelPHP, so other more experienced users might be able to give you a better answer. I don't know if it is possible to set a 'UNIQUE' constraint from the migration - like you, I had a look through the documentation, and could not find any reference to it. For the sake of good database design, this is something that I, too, would like to be able to do. I work with other programmers, and I don't want someone to come look at my tables and start picking on my work because I should have put a 'unique' constraint on a column, and the constraint is not there. On the other hand, however, simply putting the 'unique' constraint on the column is never enough in any database application. If you rely only on the database constraint, when you try to submit a record with data which is not unique, the database won't allow it, and the data insertion will fail with an error. That ensures data integrity, however, that is not very helpful to you as a programmer: from the database error you still need to find out why the error occurred (in which fields did it fail, which tables, etc.), and then give feedback to the user, allowing the user to correct their mistakes. All of this falls into the domain of DATA VALIDATION, and should usually be handled at Model level. If you have a look at the documentation for the Validation class, there is an example there of how to implement a 'unique'-type validation for a field quite easily. That validation can then be used inside your model, *before* you submit data (insert/update) to the database. Like I said, I still *would* like to be able to explicitly have the the UNIQUE constraint at the database level - I need it. But the main concern of actually implementing uniqueness check and user feedback is already possible with the tools FuelPHP has. I hope this helps.

Knud

I have just played a bit more with migrations, the DBUtils and DB classes, and have managed to come up with this (rather low-level) solution. The SQL for adding a UNIQUE constraint to a column in an existing table is quite simple:

 ALTER TABLE `tablename' ADD UNIQUE (`column name`)

It is possible, in FuelPHP, to run any SQL query against the current database, using the DB class, like this:

 DB::query("Your_Arbitrary_SQL_Query_Goes_Here")->execute()

Using the query() method above, and some raw SQL, it's possible to add unique constraints directly from the migration. Here is a step-by-step example: 1) Using Oil, we automatically generate the migration for a new table called 'members'. We are going to define just one single field in this table, called 'name':

> oil generate migration members name:varchar[100]

Oil will generate the migration file. Opening it with your code editor will reveal this:

<?php

namespace Fuel\Migrations;

class Create_members &#123;

 public function up()
 {   
  \DBUtil::create_table('members', array(
   'id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true),
   'name' => array('constraint' => 100, 'type' => 'varchar'),
   'created_at' => array('constraint' => 11, 'type' => 'int'),
   'updated_at' => array('constraint' => 11, 'type' => 'int'),
  ), array('id'));
 }

 public function down()
 {
  \DBUtil::drop_table('members');
 }
}

Note that Oil automatically added 'id', 'created_at' and 'updated_at' fields for us. 2) Just before the end of the 'up' function, add the following:

  \DB::query("ALTER TABLE `members` ADD UNIQUE (`name`)")->execute();

Your complete migration file should look like this:

<?php

namespace Fuel\Migrations;

class Create_members &#123;

 public function up()
 {  
  \DBUtil::create_table('members', array(
   'id' => array('constraint' => 11, 'type' => 'int', 'auto_increment' => true),
   'name' => array('constraint' => 100, 'type' => 'varchar'),
   'created_at' => array('constraint' => 11, 'type' => 'int'),
   'updated_at' => array('constraint' => 11, 'type' => 'int'),
  ), array('id'));
  
  //Adding UNIQUE constraint to 'name' column
  \DB::query("ALTER TABLE `members` ADD UNIQUE (`name`)")->execute();
 }

 public function down()
 {
  \DBUtil::drop_table('members');
 }
}

3) Using Oil, run the migration against your database:

> oil refine migrate:up

This works for me: the database table is defined as needed, with the unique constraint (and the index) on the 'name' column. I hope this helps.

Sariel

Intersting solution, thanks.

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.

Howdy, Stranger!

Categories

In this Discussion