Joins and Implementation of relationships in sql

We have three types of relationships in SQL.

  • one-to-one
  • one-to-many
  • many-to-many
  • To implement these relationships

    One-to-one: Use foreign key to the referenced table
    Student: sid, fname, lname, addressId
    Address: addressId, adress, city, sid

    One-to-many: Use foreign key on the “many” side of the relationship linking back to the “one” side.
    Teacher: tid, fname, lname
    Classes: cid, cname, tid    
    Note: Here table ‘Teacher’ is one side and table ‘Classes’ is on many side

    Many-to-many: Use junction table.
    The junction table stores the primary keys of each table that is involved in the relationship.
    Student: sid, fname, lname
    classes: cid, cname, tid
    student_classes: cid, sid


    Here is a very good chart of all joins (and its variations) in SQL


    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s


    Mostly technology with occasional sprinkling of other random thoughts


    Amir Amintabar's personal page

    101 Books

    Reading my way through Time Magazine's 100 Greatest Novels since 1923 (plus Ulysses)

    Seek, Plunnge and more...

    My words, my world...

    ARRM Foundation

    Do not wait for leaders; do it alone, person to person - Mother Teresa

    Executive Management

    An unexamined life is not worth living – Socrates

    Diabolical or Smart

    Nitwit, Blubber, Oddment, Tweak !!


    A topnotch site


    Just another site

    coding algorithms

    "An approximate answer to the right problem is worth a good deal more than an exact answer to an approximate problem." -- John Tukey