CODEWITHSUNDEEP
mysqldatabasedatabase-designmysql-workbench
user3687001
user3687001

Reputation: 345

Generating SQL from an ERD - how are relations mapped into SQL?

I'm using Mysql workbench EER where I draw my ERD. There are all kind of relationships between the tables (optional/mandatory,non identifying/identifying) and I use the Forward engineering to generate the underlying SQL. As far as the relationships go, for an optional relationship it generates a NULL FK while in a mandatory a NOT NULL FK and that's it.

I mean shouldn't it also based on the relationships generate Cascades deletes for example? i.e if I have an identifying relationships, then when the parent is deleted the child should be deleted too thus a cascade delete would have been generated

Or, in other words the relationships modeled in an ERD have no practical value other than conceptually know how your database is modeled on paper? For example what should be the generated sql code for an identifying mandatory relationship?

Upvotes: 1

Views: 103

Answers (1)

nvogel
nvogel

Reputation: 25534

What you are referring to is called a relationship. In database management terms a relation is something different.

Your relationships should get implemented as foreign keys. The practical value of a foreign key is that it enforces referential integrity. Cascaded deletes are not typically the desired behaviour and in SQL the default referntial integrity action is that delete of a row in a parent table is not permitted if the row is being referenced in another table. If you want cascaded deletes then you have to specify that. Note that in MySQL only the InnoDB database engine supports referential integrity.

Upvotes: 2

Related Questions