Jump to content
Larry Ullman's Book Forums

Inserting A Record With A Foreign Key Constraint


lrzins
 Share

Recommended Posts

Hi, I have a student_record table that has a field "approved_by" that has a BELONGS_TO relationship to a faculty table. My intention is to be able to create a student record for a student, and then at some point in the future, one of a list of faculty members can approve something.

 

So I have the following foreign key contraint set up on the student_record table:

 

 

CONSTRAINT

FOREIGN KEY (approved_by)

REFERENCES faculty (id)

ON DELETE SET NULL

ON UPDATE NO ACTION

 

And I find that if I try to create a new student record, it is rejected with an "Integrity constraint violation", because I have'nt provided a faculty id in the approved_by field. So obviously I have to be able to create a student record without the approved_by field being set, since this is set by a faculty member at a future time.

 

So must I remove this constraint to be able to do this?

 

My main reference is: http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html

 

I'm using MySQL 5.0.95

 

I normally use MyISAM tables, but I was eager to see the relations() entries that Gii would generate if I used InnoDB and foreign key constraints. I'm learning Yii on relatively complex schema with 24 tables and lots of relationships.

 

Thanks for any info.

 

Larry (my name is Larry too)

 

Love your books :)

Link to comment
Share on other sites

After looking into the problem more, I see now that in this relationship, the faculty table is the parent table, and the student_record table is the child table, and with a foreign key constraint, you can't insert a record in a child table without a reference to the parent table. So yes I have to remove the constraint.

 

The lesson learned here, is that you can't liberally apply foreign key constraints just so that Gii will generate the table relationships in the model files for you! I will now try using the comment method of specifying relationships in MyISAM tables for Gii:

 

approved_by int unsigned comment "constraint foreign key (approved_by) references faculty (id)",

 

I wonder if the comment method for specifying table relationships for Gii will work in InnoDB tables?

 

Cheers everybody,

 

Larry

Link to comment
Share on other sites

Just to wrap this up, I recreated the database tables with MyISAM, and used the "comment" method to declare the table relationships, and Gii outputed the exact same model files (rules and relations). I've used other PHP frameworks before, and Yii is looking very good. I'm delighted that Larry Ullman is writing a book about it. He's the best. In the future, I'll try not to use this forum as a personal blog (but hopefully it was useful to somebody).

 

Larry Z.

Link to comment
Share on other sites

Hello Larry. First of all, kudos for figuring this out and thanks for sharing everything you learned. Much appreciated. Yes, your solution was correct. The constraint is great, except that you can't constrain when the child could have a NULL value for the parent. Removing the formal constraint is right. You can then have the model allow for a NULL value, but if a non-NULL value is provided, require that it be in the parent (you may not yet be at the point where you know how to do that, but that would be an approach in time).

 

I hope you continue to like the Yii framework. Thank you very much for the nice words on my books!

Link to comment
Share on other sites

 Share

×
×
  • Create New...