SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Creating Tables in a certain order

    When you have a "parent" and a "child" table, do you have to create the "parent" table before the "child" table?

    It seems like you have to create the "parent" first so the "child" will have something to return to.


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    you must declare the referenced table before referencing it in a FOREIGN KEY declaration (in an engine which supports foreign keys)

    i'm not sure whether disabling the foreign key check will disable it during the CREATE TABLE statement, though (disabling foreign key checking is usually done when loading large volumes of data, to minimize the overhead of checking individual rows)

    still, it makes sense to create your tables in the intended order anyway
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    you must declare the referenced table before referencing it in a FOREIGN KEY declaration (in an engine which supports foreign keys)

    i'm not sure whether disabling the foreign key check will disable it during the CREATE TABLE statement, though (disabling foreign key checking is usually done when loading large volumes of data, to minimize the overhead of checking individual rows)

    still, it makes sense to create your tables in the intended order anyway
    So that was a "Yes"??


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So that was a "Yes"??
    depending on what you mean by "return to"
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,530
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    depending on what you mean by "return to"
    Quote Originally Posted by doubledee
    It seems like you have to create the "parent" first so the "child" will have something to return to.
    So the child table will have something to REFER (i.e. point) to.


    Debbie

  6. #6
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,048
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    If you are using an engine that supports foreign key constraints and using them than yes, you will need to create the table referenced by the foreign key relationship first. If you are creating "conceptual" foreign keys, but not actually enforcing them with TRUE constraints than no, you don't NEED to create the referenced table first but you should. Create a INNODB table with a foreign key constraint with a reference to a table that doesn't exist. Than do the same thing with a MYISAM table minus the foreign key constraint and see what happens.
    The only code I hate more than my own is everyone else's.


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
  •