Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2010
    Posts
    39

    Unanswered: Please give me example or link for these examples

    Dear friends,

    Can anyone please give me link or tutorials for 1-to1 and 1-to-many mysql simple example, tutorials or links please ? I tried google but I couldn't find simple ones. Please help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to begin with, a true one-to-one relationship is not possible, it will always be one-to-zero-or-one
    Code:
    CREATE TABLE users
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , name VARCHAR(99) NOT NULL
    , hatsize DECIMAL(4,3) NOT NULL DEFAULT 6.875 -- six and seven eighths
    );
    
    CREATE TABLE user_resumes
    ( id INTEGER NOT NULL PRIMARY KEY 
    , CONSTRAINT resume_user
         FOREIGN KEY ( id ) REFERENCES users ( id )
    , resume BLOB NOT NULL
    );
    a user's rows cannot simultaneously be added to both tables (with or without one of them being an auto_increment)

    a user is first added to the users table, and then subsequently added to the user_resumes table

    since id is the PK of both tables, it is unique, so the relationship is one-to-zero-or-one
    Code:
    CREATE TABLE user_phones
    ( id INTEGER NOT NULL 
    , phone VARCHAR(12) NOT NULL
    , PRIMARY KEY ( id , phone ) 
    , CONSTRAINT phone_user
         FOREIGN KEY ( id ) REFERENCES users ( id )
    );
    here a user can have multiple phone numbers (but the PK ensures that a given user cannot have the same number more than once)

    so this is a one-to-zero-or-many relationship (the "zero" bit for the same reason as above)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2010
    Posts
    39
    thank you so much r937, it helped me a lot to understand. How can i use the same scene as many-to-many relationship ?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by linux1880 View Post
    How can i use the same scene as many-to-many relationship ?
    Code:
    CREATE TABLE websites
    ( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    , url VARCHAR(99) NOT NULL
    );
    CREATE TABLE users_own_websites
    ( user_id INTEGER NOT NULL
    , site_id INTEGER NOT NULL
    , PRIMARY KEY ( user_id , site_id )
    , CONSTRAINT own_user
        FOREIGN KEY ( user_id ) REFERENCES users ( id )
    , CONSTRAINT own_site
        FOREIGN KEY ( site_id ) REFERENCES websites ( id )
    );
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2010
    Posts
    39
    sorry for the dumb question again, I could not understand
    CONSTRAINT own_user,
    CONSTRAINT resume_user
    and
    CONSTRAINT phone_user

    Are these CONSTRAINT important to relate two tables ? when for example FOREIGN KEY ( site_id ) REFERENCES websites ( id ) is relating two tables. What is CONSTRAINT and CONSTRAINT values for ?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a foreign key ~is~ a constraint, but assigning a user-defined constraint name is optional

    by specifying the constraint name explicitly, you make it easier to remove the constraint (should it ever become necessary to do so) by referring to it using the constraint name you assigned, rather than letting the constraint name be assigned some default value by the database if you don't define it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2010
    Posts
    39
    Thank you so much sir. However while looking at MySQL :: MySQL 5.1 Reference Manual :: 13.6.4.4 FOREIGN KEY Constraints it says tables must be InnoDB to use foreign key constraints, while I did not specify engine, i think default is MyISAM. Will it work or I am getting wrong ?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that is correct, foreign keys are not enforced in myisam tables

    however, the CONSTRAINT syntax may still be specified, and will be parsed for correctness(and then ignored)

    also, you can change the default table type if you wish
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jul 2010
    Posts
    39
    Thank you so much, much appreceated. I did populate all of those tables with raw data on them, what mysql commands would be best to show results relating to column of one field to another fields, should i do joins commands etc or there are other commands. Sorry for too many questions.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by linux1880 View Post
    what mysql commands would be best to show results relating to column of one field to another fields
    sorry, i do not understand "results relating to column of one field to another fields"

    i think you want a join but it's pretty hard to tell from this general description


    Quote Originally Posted by linux1880 View Post
    Sorry for too many questions.
    i think you've pretty well got the answer you wanted (about 1-to-1, 1-to-many, and many-to-many relationships)

    please don't let this thread evolve into a long tutoring session on how to do everything with databases, okay?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jul 2010
    Posts
    39
    Thank you, I have got the answer for what i asked

  12. #12
    Join Date
    Aug 2010
    Posts
    1
    Great forum! I have learned a lot here. Thanks for the information guys!!

  13. #13
    Join Date
    Aug 2010
    Posts
    1
    ahh cool just what I was looking for too. THanks

Posting Permissions

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