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…

Section		Sub-Section
--------	------------
Finance		Economy
Finance		Markets
Finance		Investing

Legal		Business Structure
Legal		Intellectual Property

But this is NOT okay…

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.

Section -||------|<- SubSection

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



with a foreign key

I don’t understand what you mean.

Can you please be a little more specific?


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…

section_id	name
1		finance
2		legal

subsection_id	name		section_id
1		economy		1
2		economy		2



a UNIQUE constraint on the subsection name column

Okay, that is what I was wondering about.