3

Is there any way to (visually) enforce candidate key constraint on an entity in an entity-relationship diagram in Visual Paradigm 12.1?

For example we have an entity called Address (street, city, state, zipcode). I want to store the attributes (id, street, city, state, zipcode) BUT I want to enforce that the combination (street X city X state X zipcode) is unique.

1 X James avenue X New York X USA X 123 is OK

2 X James avenue X New York X USA X 123 is NOT OK

This is an example in VP 12.1.

enter image description here

The problem is it means

street is unique AND
zipcode is unique AND
city is unique AND
state is unique

It should mean

Combination of street AND zipcode AND city AND state is unique.
Community
  • 1
  • 1
Slazer
  • 4,750
  • 7
  • 33
  • 60

1 Answers1

2

I'm not an expert on Visual Paradigm, but AFAICS it doesn't visually distinguish between individual and composite unique constraints.

In addition to properly specifying the composite key for the generated SQL, how about using foreground or background colors to visually associate the components of a composite key? For example:

Visual Paradigm composite key with coloring

Oracle's SQL Data Modeler displays a composite key in the same way as Visual Paradigm. MySQL Workbench does it a little better, showing indices and highlighting its components when the mouse moves over an index:

MySQL Workbench composite key

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • It will not do because the constraint will not be in the SQL when I generate the SQL from the ERD. Moreover, the generated SQL would be incorrect and the problem will show in production (not development). Is there any other tool that can do this natively? – Slazer Nov 13 '15 at 17:39
  • VP does allow you to specify composite constraints, and the generated SQL should match. I'm suggesting the use of color in addition to specifying the proper constraints, only as a visual aid. I updated my answer to clarify, and compare with two other modeling tools. – reaanb Nov 14 '15 at 05:38
  • How can I specify composite constraints (business candidate keys) in VP 12.1, so that they will be generated into SQL? – Slazer Nov 17 '15 at 18:24
  • 1
    Right-click on an entity | Open specification | Constraints | Add | Unique constraint, then type a name and add the required columns. – reaanb Nov 18 '15 at 15:03
  • Cheers for the bounty, mate! – reaanb Nov 18 '15 at 16:45