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.
So a few KEY things 🙂
- Start with the ‘parent’
- Click on the table and ‘let go’
- Now go and click on your other table
- Confirm your properties and hit OK
- 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.
32 Comments
Hi Jeff,
I know it can be done, but how can I alter the FK column name from being a concat of the parent table and column name to just that of the parent column name please?
Many thanks,
Phil
There is a template in the preferences where you can determine the default naming patterns.
Right click on your design, open the properties.
Go to Naming Standard, Templates.
Go to Foreign Key, change it to {table}_{column} .. but that would probably be the current table info, not the referenced table…you might need to poke around a bit to find that template label.
Is there an option to display the FK delete rule on the diagram (restrict, cascade, set null), ideally next to the FK arrow, but also in the table’s FK section? Most modelling tools I’ve used seem to have the option (some use R, C, N acronyms), plus it’s very pertinent to the physical model.
I don’t think so, the best advice I would have is to incorporate that into the name of the constraint, so something like name your FK
XFK_WHATEVER_CASCADE
Jeff, I really do think that the visual application of FKs in Data Modeler is the wrong way round. FKs are from a child to a parent, that’s how the syntax is defined that’s how developers / technical architects think. When you model, you may add a child table and you think “I need a FK to the parent” so you’d instinctively click on the child and drag the FK to the parent (start and end). Even in a common sense world, the FK belongs to the child, so you’d start from there (encapsulation). Oracle Data Modeler seems to do the reverse direction to all other physical data modelling tools I’ve ever used. By clicking on the parent first. Why is this?
Could you add the option to allow the visual modelling of FKs in direction they are defined? You’d drag from child to parent? It’s more intuitive and would also help those migrating from other modeling tools (which is good for Oracle). I can’t even begin to tell you the number of times I accidentally do FKs in the wrong direction in Data Modeler…
You can change the direction of the arrow/relationship, but I don’t think that changes the click – drag order when defining the relation. It’s been this way since at least 2008. I’ll ask the developer what the history/rationale behind that is (I usually make the same mistake as you, hence this post).
I know this is a few years ago, but did you ever get a reply to this?
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.
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.
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.
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.
You say existing..does that mean it works for you on a new design?
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.
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?
Answering my own question 🙂 – because it is non identyfing.
I love it when folks answer their own questions 🙂
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.
Hi Jeff,
when i right-click on a FK line i get the context menu of the diagram instead of the FK.
Regards,
Martin
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?
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.
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
ok and what’s now the solution?
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.
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
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
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?
Hi Jeff,
thanks for your reply.
When i double click on a FK line nothing happens.
Regards,
Martin
what if you right-click, is the ‘properties’ item available?
Hi Jeff,
could it be a platform specific bug? – please see https://community.oracle.com/message/12798442#12798442
Regards,
Martin
I don’t have a retina display, so that might explain why I don’t see the issue
So simply, why do more people not bother with putting FKs into their design. Go figure!
Of course this gives a great excuse to point everyone to my favorite rant – using FKs in a data warehouse. 🙂
http://kentgraziano.com/2014/03/28/better-data-modeling-my-top-3-reasons-why-you-should-put-foreign-keys-in-your-data-warehouse/