Yii_Mysql_Inoudb_Foreign_Keys

I’m working on a PHP/mysql/html5 project. I did it from scratch last year,and it works pretty well. As we started scaling up, I learned the hard way about using a design that is maintainable and scaleable. So I studied the frameworks, chose Yii, and went through the tutorials, and have been trying to port the infrastructure into a Yii box. I’m a newbie with Yii. But I did the tutorials, and got trackstar working. When I ported my mysql database to my new version 2 with frameworks, gii didn’t like foreign keys for creating the CRUD. So I removed the foreign keys columns so I could run Gii and create the CRUD infrastructure. Then I added the foreign keys back into the database. I have a “tbl_programs”, with no foreign keys, and all the CRUD works. The Programs model launches a “tbl_courses” model, controller, etc. When I attempt to view the individual courses, I get an error–

"The value for the column "id" is not supplied when querying the table "tbl_courses".

I got XDEBUG working with my XAMPP server and stepped through the php calls, seeing what is going on inside the Yii engine. The ID is being transferred all the way down, to the CDBCommandBuilder:: createInCondition. I compared the code when it reached here for both programs (which works) and courses (which breaks). The difference is there are 2 ColumnNames with primary keys, my id (which is the one its supposed to point to, to bring up the specific course to view), and the foreign key (tbl_programs_id) which helps the courses know which program they belong to. I also use it to list only the courses that belong toa program.

So my question: I think I need foreign keys. There are lots of 1 to many relationships. Does Yii have limitations in working with complex INOUDB engines? I’m also kind of new with INOUDB, my first version used mysql MYISAM, which seemed easier because I was more familiar.

Well, I guess I need some pointers to how to use Yii/mysql/inoudb/foreign keys together, because I can’t get passt this bug, and fear a major revision is necessary. And why is a foreign key being treated like a primary key.

To help you with that specific case we need to see how you define relations in the models.

The engine in MySQL your are talking about is called InnoDB, not InouDB. The main difference between InnoDB and MyISAM is that the first one supports transactions and foreign keys and is a little slower.

The main advantage of defining relations between tables using foreign keys is that you keep your data consistent. Those keys are called ‘constraints’ because they often prohibit modifying database in such way that would create “orphans”, that is records in one table that relate to non existent records in other table. So you don’t need to check it explicitly in your application. You can trust your database.

Foreign key constraints may seem annoying at first, because when you adjust your database structure while developing you often hit a lot of errors and can’t perform queries fast. But with some experience you start to balance out when to use CASCADE, not null flags and even deffering triggers to keep your data consistent.

[size=2]Thanks, yeah, I see I misspelled InnoDB several times. I’ll spend some more time on mastering the intricacies of that db engine. That seems to be one of my bigger weaknesses at the moment.[/size]

[size=2]OK, can you have 2 constraints in the same table? In tbl_courses,[/size]

I have a primary id, which is just the number of the course. I also have a foreign key, that points to program that this course belongs to. But now when I use standard yii CRUD tools, I can manage all the courses (get a listing with the grid view widget , but I get an error when I try to ‘view’ one record, it gives me the exception, even though the id number is there. This is just using the standard infrastructure built by Yii.

Please post full details of the exception, that is the message and the stack trace.