Database Migrations adding relationships & constraints

Hi there,

not sure if this goes here or under the Book discussion sub-forum. I am following the Agile Web App book, and decided to make use of the new migration features instead of relying on external SQL tools.

Creating the initial Project table was easy (once I figured out how to specify a size for a field). Further into the book, we need to create the Users, etc tables with foreign keys, constraints, etc. I have no issue creating the tables, but have no idea how to create the constraints, references, etc.

The Database Migration guide page is a good introduction. if a little thin on the full migrations capabilities and syntax. Is there a guide or page which fills in the gaps?

For those who haven’t read the book I need to turn this:




-- The Relationships

ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_project' FOREIGN KEY

('project_id') REFERENCES 'tbl_project' ('id') ON DELETE CASCADE ON

UPDATE RESTRICT;

ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_owner' FOREIGN KEY

('owner_id') REFERENCES 'tbl_user' ('id') ON DELETE CASCADE ON UPDATE

RESTRICT;

ALTER TABLE 'tbl_issue' ADD CONSTRAINT 'FK_issue_requester' FOREIGN

KEY ('requester_id') REFERENCES 'tbl_user' ('id') ON DELETE CASCADE ON

UPDATE RESTRICT;

ALTER TABLE 'tbl_project_user_assignment' ADD CONSTRAINT 'FK_project_

user' FOREIGN KEY ('project_id') REFERENCES 'tbl_project' ('id') ON

DELETE CASCADE ON UPDATE RESTRICT;

ALTER TABLE 'tbl_project_user_assignment' ADD CONSTRAINT 'FK_user_

project' FOREIGN KEY ('user_id') REFERENCES 'tbl_user' ('id') ON

DELETE CASCADE ON UPDATE RESTRICT;



into migration code. I am going to hack and experiment, but just need a little help getting started.

Thanks in advance.

Typical, minutes after creating this post I figure out the answer. The CDbMigration class has an addForeignKey method with all of the necessary parameters. Going to try that now.

Hi,

did you find a database agnostic way of specifying size for a field? Can you please post here how do you set field size in migrations? One obvious way is to replace yii’s column type with one used by DBMS, so string becomes varchar(50) for example.

edit: Just noticed it was a 3 month old post and author has been inactive for last 2 months… So if anyone else has an answer please share :).

Something like this should work. Found the answer on stackoverflow.


public function up()

{

    $this->createTable('tbl_news', array(

        'id' => 'pk',

        'title' => 'varchar(20) NOT NULL',

        'content' => 'text',

    ));

}