It’s all in the wrist, literally.

You need to know where to start and where to finish.

And you need to click ‘just’ right.

Watch the animation and see what I’m talking about.

Animated GIF showing how to draw a foreign key in Oracle SQL Developer Data Modeler.
Animated GIF showing how to draw a foreign key in Oracle SQL Developer Data Modeler.

So a few KEY things ๐Ÿ™‚

  1. Start with the ‘parent’
  2. Click on the table and ‘let go’
  3. Now go and click on your other table
  4. Confirm your properties and hit OK
  5. You now have your key!

The column in the source object will be determined by the primary key. The column in the target object will be guessed by data type and name, or column with same name in the PK. Either way, if we guess wrong, you can change using the properties dialog.

thatjeffsmith
Author

I'm a Master Product Manager at Oracle for Oracle SQL Developer. My mission is to help you and your company be more efficient with our database tools.

25 Comments

  1. I am trying to draw out a parent child relationship line between two tables. I am doing this manually in the data modeler as the child table doesn’t have a foreign key constraint on it. However, since the foreign key constraint in missing, the relationship line is not drawn between my two entities automatically when I hold the ctrl while dragging the table in my relational model screen. How do I resolve this issue? The primary key in one of my tables starts with PK_USER_ID and then in one of the other tables there is one primary key and one foreign key which is FK_USER_ID. As you see that the FK_USER_ID is related to the first table but since the constraint wasn’t setup correctly, I can’t manually add the relationship between those two tables. Every time I try doing it, it just gives me an error: “Incomplete FK definition”. Any help on this would be appreciated. Thanks.

  2. Peter Sramka Reply

    In my experience, SQL Developer is very buggy in many different ways. I am getting to the point that I am just going to have to stop using it and spend some money on something that is reliable. It is sapping too much of my time dealing with all of its bugs. Here’s my question: Is Oracle ever going to take QA with SQL Developer seriously and fix the numerous bugs? It would great if you did. The tool has a lot of nice features, it just doesn’t work correctly a lot of the time.

    • thatjeffsmith

      I use it all day every day.

      We have the lowest number of open bugs we’ve ever had in the product in a decade+

      We have a dedicated team doing QA.

      We have at least 5,000,000 users using the tool.

      I’m happy to help you, but I need specifics. Describe these bugs for starters.

  3. I cannot seem to add *any* foreign keys to an existing relational model.

    I’m following your instructions, starting with the ‘parent’, and proceeding to the table that will actually hold the foreign key constraint. The columns it picks are correct. It chooses the correct PK/UK Index on the ‘primary’ table. Everything looks good…then I click “Ok” or “Apply”, and every time it just shows an error message with the auto-generated name of the foreign key, and “Incomplete FK definition”.

    I’ve tried giving the FK a different name, drawing them the other way around, picking different tables, I can’t get it to draw a single foreign key anywhere. The tables are pre-existing, but some don’t currently have any existing foreign key constraints on them. I’ve tried ticking/unticking every combination of “Generate in DDL”, “Transferable”, “Mandatory”, etc – everything just produces this error. There’s no hint exactly what is incomplete about the foreign key definition it’s trying to generate either.

    I’m using SQL Developer 18.1.0.095.

    Any suggestions?

    Thanks.

    As a side note, changing the name and clicking “OK” shows the error message with the old name still; and the drop down “PK / UK Index” is not sorted in any discernible fashion…there are maybe 5 or 6 groupings, each sorted by themselves, but not sorted overall.

    • Yes. Just tried on a new design and it works fine.

    • All good now, I figured it out.

      Most of the tables don’t have primary keys (yes, I know…I inherited this).
      They (mostly) have unique indices though.
      Once I designated a primary key for a table, and saved the model, it allowed me to add a foreign key.
      I suppose it should just say “PK Index” instead of “PK / UK Index” though, since it doesn’t appear to work if there is only a Unique Key.

      Thanks.

  4. Jeff, on a logical diagram, what is the logic behind having the Relation line dashed for 1-1 relationships? It is not optional. This in the IE notation. Do you maybe have a recommended link for the IE notation explanation?

  5. Shawn Weeks Reply

    How do you do this in the logical model. When I create one to many relationships on the logical it creates extra columns on the physical instead of mapping to the existing column.

  6. Martin Koeb Reply

    Hi Jeff,

    when i right-click on a FK line i get the context menu of the diagram instead of the FK.

    Regards,
    Martin

    • thatjeffsmith

      ok, so sounds like you have a mouse problem – I’ve seen that with VirtualBox on my Mac before…not sure that has anything to do with us, or maybe a specific JDK?

    • Martin Koeb

      I can’t imagine that it’s a mouse problem because i don’t have problems when i install SQL Developer 3.2.20.09. With SQL Developer 4.0.2 and SQL Data Modeler 4.1.0.881 there exists the problem.

      The installed Java version is 1.8.0_31.

    • thatjeffsmith

      a mouse problem with Java 8 and retina display, it’s like the mouse pixel coordinates being sent over aren’t accurate – the other two versions you mentioned are using java 7

    • thatjeffsmith

      You could try a different jdk, there are newer java 8’s out there – but it’s only a guess as to what the problem is.

      I’m not offering a support service here, just trying to help users as I can. You probably will want to open a service request with MOS, or at least, add your 2 cents to the forums post you mentioned earlier.

    • Martin Koeb

      ok i think it’s a Oracle platform problem (JDK and/or SQL Developer/Modeler). I will downgrade to SQL Developer 3.2.20.09.

      Thanks
      Martin

  7. Martin Koeb Reply

    Hi Jeff,

    i installed Oracle Data Modeller 4.1.0.881 at my Mac. It’s possible to import from Data Dictionary but it’s not possible to edit a foreign key when i double click on a fk. It’s also not possible to rearrange a fk-line graphically. Do you have an idea what’s wrong?

    Best regards,
    Martin

    • thatjeffsmith

      I just checked on my Windows machine for that version and it works just fine. I’ll check my Mac as soon as I do a reboot, but in the middle of something.

      When you try it on your mac, what happens when you open the properties window for the FK – is everything greyed out?

    • Martin Koeb

      Hi Jeff,

      thanks for your reply.
      When i double click on a FK line nothing happens.

      Regards,
      Martin

Write A Comment