Alex Rivera | Logout

One-to-Many relationship in MySQL - how to build model?

Asked 2013-06-11T13:01:30.023
18

I have got two tables:

1) Area 2) Map

Each Area shall have at least 1 Map, but can also have more than one Map.

One Map can only belong to one Area.

How to build this in MySQL?

Edit
Report

1 Answer

16
create table Area(id int primary key auto_increment, name varchar(100));

create table Map(id int primary key auto_increment, 
                 area_id int not null,
                 name varchar(100),
                 foreign key (area_id) references area(id));

SqlFiddle

Each Map MUST have an Area, as area_id is not null (and is a Foreign key on Area)

But you won't be able (and it's not desired) to have "at least one map" for each area.

One day, you'll have to create an Area. And it won't have any Map at this time. Or make "regular" checks to see the Areas without any Map.

You may want to delete an Area, if it has no more related Map, when you delete a Map.

answered 2013-06-11T13:10:09.717

Your Answer