• Questions
  • Generating DDL in Oracle SQL Developer

Breadcrumb

Announcement

Forty years

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Barbara.

Asked: November 08, 2019 - 12:57 pm UTC

Answered by: Chris Saxon - Last updated: November 13, 2019 - 1:17 pm UTC

Category: SQL Developer - Version: 18.3.0.277

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Something new learned everyday

You Asked

Dear AskTom-Team!
Is there a possibility in the Oracle Developer to suppress duplicate DDL code? For example when generating the DDL from my relational model the foreign key constraint is generated twice, i.e. for both tables that are involved in the constraint.

Thanks
Barbara

and we said...

I'm assuming you're talking about Data Modeler?

If so, you'll have to expand your example - I've never observed this behaviour!

How exactly are you generating the DDL and what exactly is the script?


and you rated our response

  (3 ratings)

Reviews

more details?

November 08, 2019 - 5:24 pm UTC

Reviewer: thatjeffsmith from cary, nc usa

version of sql developer data modeler?

are you sure you don't have two relations accidentally defined?

it might be easier to continue this discussion on the data modeler forums page where you can upload pictures of your diagram ( https://community.oracle.com/community/groundbreakers/database/developer-tools/sql_developer_data_modeler )

Foreign key constraint

November 08, 2019 - 7:51 pm UTC

Reviewer: Barbara

I am using the datamodeler that is build in the data developer, version 18.3.0.277.
Okay, let me give you an example. When using the 'generate DDL' function in the build-in datamodeler then the foreign key constraint is generated for the parent table AND for the child table. Is this the common way a foreign key constraint should be generated in a ddl file?
Maybe I have checked or unchecked a box somewhere that causes this behaviour?!?
Connor McDonald

Followup  

November 11, 2019 - 1:50 am UTC

I did the following with 18.1. Created a basic diagram

DATA_MODELLER_Q

Then did a DDL export and got this (keeping with all defaults)

-- Generated by Oracle SQL Developer Data Modeler 18.1.0.082.1035
--   at:        2019-11-11 09:48:27 AWST
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g



CREATE user scott identified by account unlock 
;

CREATE TABLE scott.dept (
    deptno   NUMBER(2) NOT NULL,
    dname    VARCHAR2(14 BYTE),
    loc      VARCHAR2(13 BYTE)
)
PCTFREE 10 PCTUSED 40 TABLESPACE users LOGGING
    STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT );

CREATE UNIQUE INDEX scott.dept_pk ON
    scott.dept (
        deptno
    ASC )
        TABLESPACE users PCTFREE 10
            STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT )
        LOGGING;

ALTER TABLE scott.dept
    ADD CONSTRAINT dept_pk PRIMARY KEY ( deptno )
        USING INDEX scott.dept_pk;

CREATE TABLE scott.emp (
    empno      NUMBER(4) NOT NULL,
    ename      VARCHAR2(10 BYTE),
    job        VARCHAR2(9 BYTE),
    mgr        NUMBER(4),
    hiredate   DATE,
    sal        NUMBER(7,2),
    comm       NUMBER(7,2),
    deptno     NUMBER(2)
)
PCTFREE 10 PCTUSED 40 TABLESPACE users LOGGING
    STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT );

CREATE UNIQUE INDEX scott.emp_pk ON
    scott.emp (
        empno
    ASC )
        TABLESPACE users PCTFREE 10
            STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT )
        LOGGING;

ALTER TABLE scott.emp
    ADD CONSTRAINT emp_pk PRIMARY KEY ( empno )
        USING INDEX scott.emp_pk;

ALTER TABLE scott.emp
    ADD CONSTRAINT emp_fk FOREIGN KEY ( deptno )
        REFERENCES scott.dept ( deptno )
    NOT DEFERRABLE;



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                             2
-- CREATE INDEX                             2
-- ALTER TABLE                              3
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              1
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0



Maybe as Jeff suggested - throw this onto the forum with some screen dumps and we can see where things are different for you

Solved

November 13, 2019 - 7:38 am UTC

Reviewer: Barbara

The Problem is a bug in the release 18.3. of Oracle Delevoper. It is solved in newer versions.
Chris Saxon

Followup  

November 13, 2019 - 1:17 pm UTC

Thanks for letting us know.