Database Design Studio (DDS) is a tool that enforces referential integrity. It will not allow you to make the common errors many people make when creating a database manually, or with tools that do not enforce correct foreign key constraints. To understand how DDS works out the update and delete rules of a foreign constraint, you should refer to the Referential Integrity Constraints help documentation.
The commonly asked question, has to do with applying a ON DELETE CASCADE constraint to a non-prime foreign key. It is important to understand that a non-prime key column in a table is not a good candidate to use for filtering records to implicitly delete from a table, and can have serious side effects.
Consider this example:
In this case, a STORE can exist without a STAFF to manage it. But suppose STAFF 'abc' manages STORE 'xyz'. Then one day, 'abc' leaves the company and her details are DELETED from the database. Is it logical to also delete the STORE? No, rather it is logical to set the STAFF foreign key to NULL (or restrict the delete) implying that the STORE currently has no manager.
In essence, a non-prime foreign key does not imply dependence, and therefore, deleting the table rows based upon this non-prime key is inappropriate.
Now consider this example:
In this case, a SPOUSE only exists if there is a corresponding EMPLOYEE. If the EMPLOYEE quits, then there is no longer a need to keep either his or the spouse's information in the database, therefore a cascading delete is perfect and that is what you get with a weak relationship and weak entity.
Having said all of that, DDS does understand that you should be the one in control of your design, and we do therefore support the option for cascading deletes to relationships, but do warn the user to do so with great care.
See also: Foreign Primary Keys and Foreign Keys