Prevent Sub-Section from occurring in Multiple Sections

Databases
1

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?

Sincerely,

Debbie

2

with a foreign key

3

I don’t understand what you mean.

Can you please be a little more specific?

Debbie

4

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

5

So what is to prevent this…


section
--------
section_id	name
1		finance
2		legal



subsection
-----------
subsection_id	name		section_id
1		economy		1
2		economy		2

Sincerely,

Debbie

6

a UNIQUE constraint on the subsection name column

7

Okay, that is what I was wondering about.

Thanks!

Debbie