CODEWITHSUNDEEP
oracle10goracle-sqldeveloper
brillox
brillox

Reputation: 373

sql developer -data modeller generated DDL includes changes already committed into database

using the data modeller embedded in sql developer 19.4 against an oracle 12c database.

I added a couple of foreign keys within the data modeller erd diagram then I clicked the "syncrhonize data modeller with model" ; then I noticed that in the generated DDL other than my legitimate changes the DDL contain also other SQL relative to changes already existing in the database; anyway I amended the DDL by deleting the unwanted changes and applied and commit my changes. then I run the same compare again and the "Compare Models" window correctly show no changes but... the DDL has the same shanges already applied and the ones that already exists in the database.

I also reverted the comparison by comparing the model with the erd and hit "merge" thinking that this is a problem with some kind of cahced memory etc.. but same issue here: "Compare Models" view does correctly show no chaages but DDL contain changes ??

enter image description here

and below the DDL with the script of changes that already exists in the database.

ALTER TABLE gasgendev.audit_errors
    ADD CONSTRAINT audit_errors_look_audit_types_fk FOREIGN KEY ( audit_type )
        REFERENCES gasgendev.look_audit_types ( audit_type_id )
            ON DELETE CASCADE
    NOT DEFERRABLE ENABLE VALIDATE;

ALTER TABLE gasgendev.audit_logs
    ADD CONSTRAINT audit_logs_look_audit_types_fk FOREIGN KEY ( audit_type )
        REFERENCES gasgendev.look_audit_types ( audit_type_id )
            ON DELETE CASCADE
    NOT DEFERRABLE ENABLE VALIDATE;

ALTER TABLE gasgendev.halo_inputs
    ADD CONSTRAINT halo_inputs_look_assets_fk FOREIGN KEY ( look_assets_l_asset_id )
        REFERENCES gasgendev.look_assets ( l_asset_id )
            ON DELETE CASCADE
    NOT DEFERRABLE ENABLE VALIDATE;

ALTER TABLE gasgendev.halo_inputs
    ADD CONSTRAINT halo_inputs_look_datasets_fk FOREIGN KEY ( dataset_id )
        REFERENCES gasgendev.look_datasets ( l_dataset_id )
            ON DELETE CASCADE
    NOT DEFERRABLE ENABLE VALIDATE;

ALTER TABLE gasgendev.manual_inputs
    ADD CONSTRAINT manual_inputs_look_manual_inputs_fk FOREIGN KEY ( look_manual_inputs_look_manual_input_id )
        REFERENCES gasgendev.look_manual_inputs ( look_manual_input_id )
            ON DELETE CASCADE
    NOT DEFERRABLE ENABLE VALIDATE;

ALTER TABLE gasgendev.manual_inputs
    ADD CONSTRAINT manual_inputs_look_datasets_fk FOREIGN KEY ( dataset_id )
        REFERENCES gasgendev.look_datasets ( l_dataset_id )
            ON DELETE CASCADE
    NOT DEFERRABLE ENABLE VALIDATE;

Of course if I run that script in SQL developer I get errors stating that those constraints already exists.

Can anyone tell me what i am doing wrong here?

Upvotes: 4

Views: 701

Answers (1)

Thoth
Thoth

Reputation: 2276

This error means that either your Data Modeller or your SQL Dev are out of date, causing them to not cooperate. The constraints are there, but your DM isn't recognizing them. Update your system, and if that doesn't work, reinstall each manually. I've seen this happen once before, and it wasn't pretty. Hope this fixes your problem!

Upvotes: 1

Related Questions