How can implement one-to-many, mandatory-to-mandatory in MYSQL?

Question

I have relationship one-to-many, mandatory-to-mandatory,So I create staff_ID as foregin key in the position and make it not null, after I finish, I used dbforge program to auto generate diagram for my database and I see the releation is one-to-many, mandatory-to-optional How can I make it in the Both side mandatory?

enter image description here

Check my answer here: [In SQL, is it OK for two tables to refer to each other?](http://stackoverflow.com/questions/10446641/in-sql-is-it-ok-for-two-tables-to-refer-to-each-other). Read also the link in (third approach) of how it can be done with deferrable constraints in Postgres. — ypercubeᵀᴹ, Nov 29 '14 at 15:14

score -1 · Answer 1 · answered Nov 29 '14 at 13:33

You need a junction table for this. If it were a one-to-one relationship, then you could do it by putting a position-id in the staff table, but for one-to-many you need a third table:

staff
----------------------------------------------
staff_id (PK) (FK referencing staff_positions)
fname
lname
...

position
-----------
position_id (PK) (FK referencing staff_positions)
position_name

staff_positions
---------------
staff_id (PK) (FK referencing staff)
position_id (PK) (FK referencing position)

How can implement one-to-many, mandatory-to-mandatory in MYSQL?

1 Answers1