2

The diagram has over 40 tables, most of them have a primary key defined.

For some reason there is this one table, which has a primary key defined, but that's being ignored when I export the model to a DDL script.

This is the "offending" key (even though it's checked it is nowhere to be found on the generated DDL script):

enter image description here

Has anybody had the same problem? Any ideas on how to solve it?

[EDIT] This is where the key is defined:

enter image description here

And this is the DDL preview (yes, the primary key shows up there):

enter image description here

This is what happens if I try to generate the DDL for just that table (primary key still not generated):

enter image description here

NotGaeL
  • 8,344
  • 5
  • 40
  • 70
  • I could guess, but it would be easier if you showed us where the PK is defined in the model. So open the properties of the table in the model, and show us where MMS_PK is defined. Also, if you right-click on the MIEMBROS table in the diagram and say 'preview DDL', so the PK code show up there? – thatjeffsmith Aug 20 '15 at 13:15
  • Added both. The primary key shows up in the DDL preview of the table. – NotGaeL Aug 20 '15 at 14:05
  • And what do you get when you generate the DDL for just that one table? And on the generate dialog, there's a PK tab - is your PK checked there? – thatjeffsmith Aug 20 '15 at 15:28
  • File->export->DDL file->OK – NotGaeL Aug 20 '15 at 15:42
  • (the PK and UK constraints is on the first screen shot, as you can see the key is selected) – NotGaeL Aug 20 '15 at 15:58
  • If I select only that table I have the same problem: The key is not generated. – NotGaeL Aug 20 '15 at 16:00
  • Only the foreign keys, the unique index and the sequence to autoincrement the primary key column are generated. I added an extra screenshot showing it. – NotGaeL Aug 20 '15 at 16:03
  • (I made sure the PK was checked on the PK and UK Constraints tab) – NotGaeL Aug 20 '15 at 16:08
  • 1
    use the ddl preview or export as a workaround, and add a thread to our Data Modeler space on the OTN forums. we can get more details on your install there and fix a bug if it there is one – thatjeffsmith Aug 20 '15 at 16:47
  • Has there been any advancements with this issue or a ticket created on the OTN forums? I'm facing it now, though I don't have any relationships going both ways in my design. DDL preview has the PKs, but the DDL export doesn't. I'm using JDK 1.8_0_60, SQL Developer 4.1.2.20.64. Thanks! – Edu Castrillon Nov 09 '15 at 12:24
  • No tickets were created. I marked the issue as solved since removing the mandatory check on the offending foreign key definition solved the problem. A ticket should be created though, because I have noticed this and other silent fails when generating a wrongly defined schema (i.e. with conflicting definitions). – NotGaeL Dec 03 '15 at 15:11
  • for more info, check out my post on the OTN forums: https://community.oracle.com/message/13286786#13286786 – NotGaeL Dec 03 '15 at 15:13

3 Answers3

1

I was finally able to identify and reproduce the problem.

It was a simple conflict of constraints.

Table MIEMBROS had a mandatory 1 to n relationship (foreign key) from another table on its primary key column and vice-versa (there was a foreign key on MIEMBROS against the other table's primary key).

This kind of relationship between two tables makes it impossible to add a record to any of them: The insert operation will return an error complaining about the foreign key restriction pointing the other table.

Anyway I realized that one of the relationships was 0 to n so I simply unchecked the "mandatory" checkbox on the foreign key definition and everything went fine.

So, in a nutshell: The Data Modeler "fails" silently if you are defining a mutual relationship (two foreign keys, one on each table against the other table) on non nullable unique columns, by not generating the primary key of one of the tables.

Such an odd behavior, if you ask me!

NotGaeL
  • 8,344
  • 5
  • 40
  • 70
1

"This kind of relationship between two tables makes it impossible to add a record to any of them: The insert operation will return an error complaining about the foreign key restriction pointing the other table."

Actually, if you have deferred constraints, this is not impossible. The constraints can be enforced, for example, at commit time rather than immediately at insert time.

Bill Koster
  • 21
  • 2
  • That's right. Do you know how to specify deferred constraints in modeler? – NotGaeL May 24 '16 at 10:13
  • No, I've never seen an option for doing that. If I were actually going to put it in the model, I'd add a dynamic property to identify a deferred constraint and define a custom DDL transformation to handle it. – Bill Koster May 31 '16 at 11:54
0

From the Data Modeler menu under File, I used Export -> DDL File. The keys appeared in the DDL, then when I went back to the diagram and did DDL Preview, it showed all the missing stuff.

Shaun Goodbrand
  • 1
  • that didn't work for me. File -> Export -> DDL File was what I was using in the first place, and the keys didn't show. – NotGaeL Sep 03 '15 at 20:07