SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: Key constrains

  1. #1
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Key constrains

    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!!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sir_Arcturua View Post
    Should I define key constraints in both parents and child tables?
    no, only in child tables

    Quote Originally Posted by Sir_Arcturua View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sir_Arcturua View Post
    Do you mean I should not define any key constrain?
    no, you should


    Quote Originally Posted by Sir_Arcturua View Post
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Sir_Arcturua View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    May 2011
    Posts
    242
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot!!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •