Question about Relational Database and Foreign Key Creation

Hello, this is a really noobie Yii question.

When I create a relational database in Yii, do I create the foreign key in the appropriate table using a DB management system or query, and then declare it in Yii? OR do I just make the tables and primary keys, and then declare the foreign key in Yii?


If you already have designed your database,including the declaration of foreign keys, primary keys, indexes, etc., Yii will automatically follow your design when you create the model for your tables. If your table A is related to table B, and you created a model for table A, Yii will respect that and create a relation based on your design. In that way, you don’t need to declare anything again in your codes if you simply want to follow your database structure.

Of course there are instances wherein you need to include the relation or foreign keys manually, like when you changed your mind and you want to relate table A to table C instead of table B. But to simply answer your question, just make the tables and declare the keys, then create a model, then verify the created model. :)

I have the same problem. I created two tables with relations FK and PK but once I generate the yii model, yii does not put anything in relation function!!

That’s what I get after generating the model

public function relations()


		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(



Do you use MyISAM tables or InnoDb? MyISAM does not support FK constraints, but won’t “scream” if you try to add them. Maybe that’s the reason

I am using MySQL workbench on a Mac!!

If you use MySQL workbench to create the database scheme it will select InnoDb as default for data engine.

What I did is to create an EER model, Define the relation then sync the model to update the "source" (my schema and tables).

Then get gii to generate the model.

I think this way, I should get the relations function already filled,right?

As someone already stated, make sure you’re using InnoDB database engine. In order to check which engine is set for your EER, in MySQL Workbench go to Model->Model Options and then check the Model: MySQL tab.

That should tell you what engine your EER is using. You can then uncheck the “Use Global Settings” option at the bottom of the window and set InnoDB for that model in case isn’t already set. Otherwise you can change your MySQL Workbench global options to use InnoDB in all your EER models (recommended) .

Yup. It is using InnoDB by default. It still does not see the relation. Not sure why but I may have to enter the relations manually. On the other hand, it works fine on a Windows machine but not sure why on Mac , Yii does not see the relation(s).!!!

Well, that’s weird… you should also check the database itself, not just the EER. Maybe the model’s engine is set correctly, but the database has InnoDB disabled or something.

Yes it is. Even in MySQL Workbench preferences Default Storage Engine: is set to InnoDB. Everything on the model and my schema works just fine. I had to copy the relation function from the Windows Machine and seems it is working over there !!

That’s a weird issue, it works fine on my mac o.o … at least you worked it out tho :).

Are you using XAMPP on a Mac?

Still long way to get the project done but at least the relation is working.

Let me explain the actual project then you get a better picture of what I have in hand

I have a model(table) called invoice. I have another model(table) called company. I need to be able to show/search/sort companies in invoice gridview and then later on add a dropdown list where normally there is a textbox to filter.

Also I have another model named paymentStatus with two fields and two rows





and we filled the table like this



I also need to filter the invoice gridview by Paymentstatus.

The general idea is to access different fields of different models and show/search/sort by each of them hopefully with a dropdown list.

Let me know if it is not clear then I will add more if you want to know more.

Thanks a bunch

This is the first step:


Then, in order to replace the textboxs with dropdownlists, you have to customize your grid columns, like this:

// controller - admin action - before render

$values = CHtml::listData(Model::model()->findAll(), 'id', 'name')

// make sure you send the $values along with your other variables to the view when calling $this->render()

// admin view


$this->widget('zii.widgets.grid.CGridView', array(

    'id' => 'this-is-a-nice-grid',

    'dataProvider' => $model->search(),

    'filter' => $model,

    'columns' => array(



            'name' => 'related.attribute', //where "related" is the name of the relation in your model and attribute, well, the attribute <img src='' class='bbc_emoticon' alt=':D' />

            'value' => '$data->relatedModel->attribute',

            'filter' => CHtml::activeDropDownList($model, 'id_of_related_model', $values, array('empty' => 'Show All')),



            'class' => 'CButtonColumn',





PS: I’m not using XAMPP on mac, just the apache that comes shipped with the macbook with some custom configuration :B

Thank you. I am going to set two fresh models tomorrow at work just to test how the idea work and I hope I we hack walk thru the two examples. Then I can learn :)

if you dont mind

I have created two tables namely tbl_Test1 and tbl_Test2.

The tbl_Test1 has three fields:

test1id INT(11) PK for tbl_Test1

test2_id INT(11) FK for tbl_test2

name VARCHAR(45) a random field

AND tbl_Test2 has three fields:

test2id INT(11) PK for tbl_test2

fname VARCHAR(45) a random field

lname VARCHAR(45) a random field

The relation is set up hence in modela\Test1.php we have

public function relations()


		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

            'test2'=>array(self::BELONGS_TO, 'Test2','test2_id'),



Now the task is to show fields in tbl_Test2 in Test1 gridview with a dropdown list. That is a dropdown list that user can select fname another one which shows lname then Test1 model can filter the gridview based on what user has selected.

I will fill tbl_Test2 to have duplicate fnames and duplicate lname to test the relation.

Thank you in advance