Key constrains

[Sir_Arcturua]

Good day,

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]

Should I define key constraints in both parents and child tables?

no, only in 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

[Sir_Arcturua]

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]

Do you mean I should not define any key constrain?

no, you should

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.

i'm saying omit those, and let them take whatever the default is

exactly where do you see these options?

[Sir_Arcturua]

Hi,

I am working in CPanel, phpMyAdmin. Select database, select table, select "structure" tab and then press "Relation view" button to see Relations page.

Thanks!!

[r937]

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]

Understood, thanks!

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]

If I wanted to change something in one of the parent tables, I will be restricted by the relationship with the child table.

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]

Thanks a lot!!!