SitePoint Sponsor

User Tag List

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

    Prevent Sub-Section from occurring in Multiple Sections

    My website currently organizes Articles by "Section", and to further divide things, I want to introduce the concept of a "Sub-Section".

    However, I want to ensure that any given "Sub-Section" can only ever be associated with one parent "Section".

    For example, this is okay...
    Code:
    Section		Sub-Section
    --------	------------
    Finance		Economy
    Finance		Markets
    Finance		Investing
    
    Legal		Business Structure
    Legal		Intellectual Property

    But this is NOT okay...
    Code:
    Section		Sub-Section
    --------	------------
    Finance		Economy
    
    Legal		Economy

    As I see it, there is a simple "one-to-many" relations between my "Section" and "Sub-Section" tables.

    Code:
    Section -||------|<- SubSection

    How do I enforce this Business Constraint (in MySQL) mentioned above?

    Sincerely,


    Debbie

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    How do I enforce this Business Constraint (in MySQL) mentioned above?
    with a foreign key
    rudy.ca | @rudydotca
    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,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    with a foreign key
    I don't understand what you mean.

    Can you please be a little more specific?


    Debbie

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    As I see it, there is a simple "one-to-many" relations between my "Section" and "Sub-Section" tables.
    okay, more specific

    Section table has a PK, let's call it section_id

    Sub-Section table has a FK, let's call it section_id

    therefore, each subsection can belong to only one section
    rudy.ca | @rudydotca
    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,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, more specific

    Section table has a PK, let's call it section_id

    Sub-Section table has a FK, let's call it section_id

    therefore, each subsection can belong to only one section
    So what is to prevent this...

    Code:
    section
    --------
    section_id	name
    1		finance
    2		legal
    
    
    
    subsection
    -----------
    subsection_id	name		section_id
    1		economy		1
    2		economy		2
    Sincerely,


    Debbie

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    So what is to prevent this...
    a UNIQUE constraint on the subsection name column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,931
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    a UNIQUE constraint on the subsection name column
    Okay, that is what I was wondering about.

    Thanks!


    Debbie


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
  •