Relational Active Record without a foreign key

Archives Yii 1.0.x Discussions
1

For my project, I don’t want to use foreign keys in the tables due to performance degradation and, in my opinion, no real need for them. I have these two tables:




CREATE TABLE events_specials

(

thisid INT unsigned NOT NULL AUTO_INCREMENT,

id INT unsigned NOT NULL,

hour_id INT unsigned NULL,

thisname VARCHAR(80) NOT NULL,

thisdesc TEXT NULL,

thistype TINYINT(1) unsigned NOT NULL DEFAULT 0,	

PRIMARY KEY (thisid),

INDEX (id)

) ENGINE=InnoDB;


CREATE TABLE hours

(

id INT unsigned NOT NULL,

date_start DATE NULL,

date_end DATE NULL,

time_start SMALLINT(4) unsigned NULL,

time_end SMALLINT(4) unsigned NULL,

all_day TINYINT(1) unsigned NULL,

daily TINYINT(1) unsigned NULL,

monthly TINYINT(1) unsigned NULL,

yearly TINYINT(1) unsigned NULL,

days_su TINYINT(1) unsigned NULL,

days_mo TINYINT(1) unsigned NULL,

days_tu TINYINT(1) unsigned NULL,

days_we TINYINT(1) unsigned NULL,

days_th TINYINT(1) unsigned NULL,

days_fr TINYINT(1) unsigned NULL,

days_sa TINYINT(1) unsigned NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

which essentially means that events_specials.hour_id refers to the primary key of Hours. I want to load hours along with the events_specials information in the CRUD if possible, which seems like it would use RAR. However, the RAR documenation seems to rely on foreign keys (even though it says it can be used without it). How could I implement it in this situation?

2

You can add a comment to a column declaration.

http://www.yiiframework.com/doc/guide/database.arr

/Tommy

3

Change the InnoDb to MyIsam and all foreign keys are nulled.

4

Actually, you don’t even need to declare such comment.

You just need to declare the foreign key correctly in relations(). It is not required you also have the FK constraint defined in DB.

5

How do I declare the foreign key if there is none, though… or are we talking about a ‘pseudo’ foreign key that doesn’t cause MySQL to do it’s extra verification work? Would I just put events_specials.hours_id in as the foreign key for hours?

6

yes, it’s pseudo FK that you only declare in relations(), just as you usually do when there is true FK constraint.

7

Excellent, thanks :)