CODEWITHSUNDEEP
oracle-databaseoracle-sql-data-modeler
NotGaeL
NotGaeL

Reputation: 8484

Oracle SQL Data Modeler missing a PRIMARY KEY on DDL script export

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

Upvotes: 2

Views: 2399

Answers (3)

Bill Koster
Bill Koster

Reputation: 21

"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.

Upvotes: 1

NotGaeL
NotGaeL

Reputation: 8484

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!

Upvotes: 1

Shaun Goodbrand
Shaun Goodbrand

Reputation: 1

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.

Upvotes: 0

Related Questions