1

I have a many-to-many relationship, and one direction is mandatory, like the following diagrams, conceptual model and physical model. A UserAccount has to have one or more Permission to it. When we insert a UserAccount, how can we enforce at least one Permission is assigned to it? This enforcement should be in database or in our client code? I will use Hibernate.

I am using PowerDesigner for this modeling and it generated the database sql for me and it looks like there is no such constraint for insert. If PowerDesigner can help, how to do it?

enter image description here

enter image description here

pascal
  • 3,287
  • 1
  • 17
  • 35
5YrsLaterDBA
  • 33,370
  • 43
  • 136
  • 210
  • which database are you using? – pascal Apr 08 '15 at 16:40
  • I don't think it can be generated automatically by PowerDesigner. – pascal Apr 08 '15 at 17:04
  • To ensure the integrity of the database, you can add an insert trigger on UserAccount to verify that there exists a related Permission; and a delete trigger on UserAccount_Permission to verify that it does not leave a UserAccount without Permission... The problem is that triggers would have to be trigger at commit-time, if supported by your database. **Else** you have an insertion problem: you cannot insert a UserAccount without UserAccount_Permission, but you cannot insert a UserAccount_Permission without the referenced UserAccount... – pascal Apr 08 '15 at 17:07
  • I am using MariaDB or MySQL database. – 5YrsLaterDBA Apr 08 '15 at 20:35

0 Answers0