Skip to Main Content
  • Questions
  • Generating DDL in Oracle SQL Developer

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Barbara.

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

Last updated: January 21, 2020 - 10:16 am UTC

Version: 18.3.0.277

Viewed 10K+ times! This question is

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 Chris 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?


Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

more details?

thatjeffsmith, November 08, 2019 - 5:24 pm UTC

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

Barbara, November 08, 2019 - 7:51 pm UTC

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

Barbara, November 13, 2019 - 7:38 am UTC

The Problem is a bug in the release 18.3. of Oracle Delevoper. It is solved in newer versions.
Chris Saxon
November 13, 2019 - 1:17 pm UTC

Thanks for letting us know.

regarding the DDL migration using "Scratch Editor" option

Rajeshwaran, Jeyabal, January 16, 2020 - 9:08 am UTC

Team,

Using Oracle SQL Developer 19.4 in this demo.

Here is my sql server ddl :

create table t( 
    c1 int identity(1,1) not null, 
    y date ) ;


when using the Tools->migration->scratch Editor - got the below in the output.

Also set Tools->preferences->migration->Translators - have set/checked the "Identify columns" check box also.

dont see the identify columns translated as such, still see the trigger based approach - kindly advice.
CREATE TABLE t
(
  c1 NUMBER(10,0) NOT NULL,
  Y DATE 
);


/* Translation Extracted DDL For Required Objects*/
CREATE SEQUENCE tt_t_c1
   START WITH 1 
   INCREMENT BY 1;
/
CREATE OR REPLACE TRIGGER 
 tt_TR_1    BEFORE INSERT 
   ON t
   FOR EACH ROW
   BEGIN
      SELECT tt_t_c1.NEXTVAL INTO :NEW.c1
        FROM DUAL;
   END;
/

Connor McDonald
January 21, 2020 - 10:16 am UTC

I spoke to the SQL Dev people.

The scratch editor *should* obey the preferences.

Please raise an SR to get a bug logged. (They carry more weight coming from customers than if they come from me)

regarding the DDL migration using "Scratch Editor" option

Rajeshwaran, Jeyabal, January 22, 2020 - 12:38 am UTC

Thanks Connor. will do the same.