I have got two tables:
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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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));
Map MUST have an
area_id is not null (and is a
Foreign key on
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
Add a Foreign key in Map that references the Area’s Primary Key. That will enforce a one-to-many relationship between Maps and Areas.
As for enforcing a minimum of one map per area (if that is necessary) there are some ideas in this post here. One of the simpler solutions would be to create a view that only displays areas which have maps:
CREATE VIEW viewAreas AS SELECT * FROM Areas, Maps WHERE Areas.ID = Maps.AreaID;
This way, you can create an area, and then add maps to it. You can also enforce the Foreign Key in maps to be NOT NULL, so a map must always have an area.
A table each for Map and Area, with a foreign key on Map linking to Area.