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

mysql relational-database
tellob picture tellob · Jun 11, 2013 · Viewed 27.5k times · Source

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?

Answer

Raphaël Althaus picture Raphaël Althaus · Jun 11, 2013 
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.

Related questions

How to create relationships in MySQL

In class, we are all 'studying' databases, and everyone is using Access. Bored with this, I am trying to do what the rest of the class is doing, but with raw SQL commands with MySQL instead of using Access. I …

Read More
Setting up table relations what do "Cascade", "Set Null" and "Restrict" do?

I want to start using table relations in a new project. After some googling I got 2 tables set up as InnoDB: The keys I want to link are ->users->userid (primary) ->sessions->userid (index) …

Read More
MySQL query, MAX() + GROUP BY

Daft SQL question. I have a table like so ('pid' is auto-increment primary col) CREATE TABLE theTable ( `pid` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `cost` INT UNSIGNED NOT NULL, `rid` INT NOT NULL, ) Engine=InnoDB; …

Read More