sir_arcturua — 2012-06-20T12:48:33-04:00 — #1
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?
Thanks a lot!!
r937 — 2012-06-20T12:53:20-04:00 — #2
no, only in child tables
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
sir_arcturua — 2012-06-20T13:14:32-04:00 — #3
Thanks a lot for your answer!
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.
r937 — 2012-06-20T13:35:57-04:00 — #4
no, you should
i'm saying omit those, and let them take whatever the default is
exactly where do you see these options?
sir_arcturua — 2012-06-20T14:05:35-04:00 — #5
I am working in CPanel, phpMyAdmin. Select database, select table, select "structure" tab and then press "Relation view" button to see Relations page.
r937 — 2012-06-20T14:39:00-04:00 — #6
darn it holmes, just as i feared -- learning how databases work via the phpmyadmin ptui user-friendly interface
choose the RESTRICT option, because that's mysql's default
sir_arcturua — 2012-06-20T15:17:12-04:00 — #7
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?
r937 — 2012-06-20T15:27:47-04:00 — #8
this is true ~only~ if you are trying to update or delete the value of a primary key column -- all other changes to the parent table are okay
sir_arcturua — 2012-06-20T19:53:01-04:00 — #9