I’ve done most of my database programming with MySQL MyISAM tables. As I’m taking up Yii for the first time, I’m also making the leap to InnoDB tables.
I might not be understanding Foreign Key constraints. Or, at least, with Yii Active Record, I’m seeing “cannot delete parent row” errors that I don’t think I should be seeing.
Suppose I am importing sales data and saving it in the database. One of the imported items is the agent who made the sale. I created a second table (normalization) to hold the agent name, and the sales table stores the row id of that agent.
The agent table might look like this:
id, auto increment primary key
agent_name, varchar(255)
I then set up the sale table to look like this:
id, auto increment primary key
…various columns of sale data…
t_agent_id, int not null
ADD CONSTRAINT fk_sale_agent_id FOREIGN KEY (t_agent_id) REFERENCES agent (id) ON DELETE NO ACTION ON UPDATE NO ACTION
So, in the sale table, I could have many sales which all reference the same agent who made the sale.
The models and crud and views all look like the correct relationships are set up as generated by gii with giix extension.
It seems to me that I should be able to delete a row of the sale table without deleting the referenced agent row. After all, that agent could have hundreds of sales, and I ought to be able to delete one of those sales without deleting the agent.
That’s where I get an error message to the effect that I can’t delete the parent row because of the foreign key constraint. I apologize for being vague here: I was trying all sorts of things trying to understand what the issue was, and I’m not totally clear on what caused what.
My solution, so far, has been to simply delete the foreign keys. The generated code keeps the relationship, so joins still work automatically. Nice! However, my solution is obviously WRONG. You can’t fix an integrity problem by removing the integrity check!
I established a foreign key expressing a many-to-one relationship, because on the view showing the sale, I want it to follow the table join to display the agent name.
I’ve done a fair amount of searching documentation and tutorials, and I can’t find an answer that makes sense in setting up this relationship.
Some answers imply that I have it backwards. In other words, the foreign key should be attached to the agent table expressing a one-to-many relationship to the sale table. Is that the right answer, and can gii then reverse-engineer the sale model/view to follow the agent id to the agent name?
Some answers imply that I should have foreign key constraints on both tables, pointing to each other. Intuitively, to me, that does not make sense.
To summarize, my situation is this:
-
As part of standard normalization, I took a repeating data item and put it in a second table. I then point to the item by placing the value of that row’s primary key in the first table. (I’m doing this many times with a few dozen tables, but if I can do this correctly for two tables, I can do it for the rest.)
-
I am expressing the relationships via InnoDB Foreign Keys. This is my first time working with foreign keys in MySQL, and there is very likely something here which I’m not understanding.
-
I am expecting Yii generated code to take this all in, and have everything hang together as intended. So far this part is looking good.
-
I get the concept of cascading deletes, but I’m specifying ON DELETE NO ACTION. It makes no sense for the deletion of one sale row to delete the agent row. This, again, warns me that I might have the whole arrangement backward.
Hmm… let me take it one step further. I really have a web of interconnected tables.
Suppose I have both a sale table, and a commissions-paid table, which both point to the agent table. Both sales, and commissions, are attached to a specific agent, and that agent has a row in the agent table.
The way I have it now, the sale table has a foreign key pointing to the agent table, and the commission table also has a foreign key pointing to the agent table.
Should I have it the other way around? That is, the agent table should have two foreign keys, one pointing to the sale table and one pointing to the commission table? Then it would make sense for the constraint to specify something like ON UPDATE SET NULL (which I believe means set the item in the other table to null when deleting the row of this table).
And, if I set it up that way, can the model/crud generators for the sale table figure out the sale table’s relation to the agent table, even though the foreign key is attached to the agent table?
Thanks for any help or links to documentation you can suggest!
Ed Barnard
Cannon Falls Minnesota