I am developing a bookstore website using PHP/MySQL. I have set some tables in MySQL and I’d like to ask about key constraints.
I created some testing tables I to define how to set key constrains:
There are two parent tables with one primary key each and no foreign keys
There is one child table with no primary key and two foreing keys, one from each parent table.
I’d like to be able to load .csv files using MySQL importing feature in all three tables, and also delete/change information from each of them.
Here my questions:
Should I define key constraints in both parents and child tables?
What should I set in each case for ON DELETE and ON UPDATE?
for the time being, don’t bother with this part, leave it out and let it use the defaults
if you are just learning, the main thing is to see how a foreign key works “out of the box”
for example, if a parent row has one or more child rows, and you try to delete the parent row, what normally happens is that it won’t let you do that, because the child row would then be an orphan, its foreign key to the parent row would be invalid
this is called relational integrity
there are options to override the “out of the box” behaviour, but you don’t need to learn them yet
When you say:
“for the time being, don’t bother with this part, leave it out and let it use the defaults”
Do you mean I should not define any key constrain? (I guess I should. If not, how could I set the relationship)
Or do you mean I should set ON DELETE no action and ON UPDATE no action? It also gives me “castade”, “set null” and “restrict” options.
One last question. These three sample tables contain static information, it is, not updated by the website when in use. Just to read from them.
If I wanted to change something in one of the parent tables, I will be restricted by the relationship with the child table. How could I do it?